使用PasteSpecial粘贴值而不是公式-VBANewbie [英] Paste Values instead of formulas with PasteSpecial - VBANewbie
问题描述
我绝对是vba的新手.我想使用以下代码将单元格中的某些值从两个选项卡("Equities","Bonds")复制到第三个选项卡("ZSM")中.
I am absolutely new to vba. I want to copy certain values in cells from two tabs ("Equities", "Bonds") into a third one ("ZSM") with the following code.
Sub AllesAufEinmal()
Call Spalten
Call Wertpapiere
Call Daten
End Sub
Sub Spalten()
'
' Spalten Macro
'
Sheets("Equities").Select
Range("A4").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("ZSM").Select
Range("A4").Select
ActiveSheet.Paste
Range("A4").Select
Sheets("Bonds").Select
Range("B4").Select
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("ZSM").Select
Selection.End(xlToRight).Select
ActiveCell.Offset(0, 1).Select
ActiveSheet.Paste
Range("A4").Select
End Sub
Sub Wertpapiere()
'
' Wertpapiere Macro
'
'
Sheets("Equities").Select
Range("A5").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("ZSM").Select
Range("A5").Select
ActiveSheet.Paste
Range("A5").Select
Sheets("Bonds").Select
Range("A5").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("ZSM").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
Range("A5").Select
End Sub
Sub Daten()
'
' Daten Macro
'
'
Sheets("Equities").Select
Range("B5").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("ZSM").Select
Range("B5").Select
ActiveSheet.Paste
Sheets("Bonds").Select
Range("B5").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("ZSM").Select
Range("B5").Select
Selection.End(xlDown).Select
Selection.End(xlToRight).Select
ActiveCell.Offset(1, 1).Select
ActiveSheet.Paste
End Sub
这很好,直到我想以某种方式修改代码,以便我的vba代码将两个选项卡("Equities,Bonds")中的公式值复制到我的第三个选项卡("ZSM")中.我真的只希望该公式从"= J5 * K24"之类的公式中返回的值被复制.即使我以以下方式修改了代码(以"### here"标记的更改),该代码也无法正常工作:
That works fine until I wanted to modify the code in a way so that my vba code copies the values from my formulas in the two tabs ("Equities, Bonds") into my third tab ("ZSM"). I really only want the value the formula gives back from formulas like "= J5*K24" to be copied. That did not work even though I modified the code the following way (changes marked with "###here"):
Sub AllesAufEinmal()
Call Spalten
Call Wertpapiere
Call Daten
End Sub
Sub Spalten()
'
' Spalten Macro
'
Sheets("Equities").Select
Range("A4").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("ZSM").Select
Range("A4").Select
ActiveSheet.Paste
Range("A4").Select
Sheets("Bonds").Select
Range("B4").Select
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("ZSM").Select
Selection.End(xlToRight).Select
ActiveCell.Offset(0, 1).Select
ActiveSheet.Paste
Range("A4").Select
End Sub
Sub Wertpapiere()
'
' Wertpapiere Macro
'
'
Sheets("Equities").Select
Range("A5").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("ZSM").Select
Range("A5").Select
ActiveSheet.Paste
Range("A5").Select
Sheets("Bonds").Select
Range("A5").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("ZSM").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
Range("A5").Select
End Sub
Sub Daten()
'
' Daten Macro
'
'
Sheets("Equities").Select
Range("B5").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("ZSM").Select
Range("B5").Select
ActiveSheet.PasteSpecial ###here
Sheets("Bonds").Select
Range("B5").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("ZSM").Select
Range("B5").Select
Selection.End(xlDown).Select
Selection.End(xlToRight).Select
ActiveCell.Offset(1, 1).Select
ActiveSheet.PasteSpecial ###here
End Sub
有什么想法吗?我读了一些有关PasteSpecial Methode的信息,但目前无法将其应用于我的问题.
Any ideas? I read a bit about the PasteSpecial Methode but could not apply it to my problem at this stage.
感谢您的帮助!非常感谢您的支持.
Thank your for your help! I would really appreciate your support.
按要求截屏 注意:在"ZSM"选项卡中,ISIN列仅应存在一次.应该可以扩展列和行.
Screenshots as requested Attention: The column ISIN should only be there once in tab "ZSM". It should be possible to extend columns and rows.
推荐答案
使用您的最后一个问题中描述的直接价值转移方法,我想出了这一点.
Using the direct value transfer methods described in your last question, I've come up with this.
传输的每个部分都有标签,因此您可以根据需要将各个例程分开.
Each part of the transfer is labelled so you can split the individual routines apart as needed.
Option Explicit
Sub AllesAufEinmal()
Dim tws As Worksheet
Set tws = Worksheets("ZSM")
Call Spalten(tws)
'Call Wertpapiere(tws)
'Call Daten(tws)
End Sub
Sub Spalten(zsm As Worksheet)
' Spalten Macro
'headers, ISIN and data from from Equities
With Worksheets("Equities")
With .Range(.Cells(.Rows.Count, "A").End(xlUp), .Cells(4, .Columns.Count).End(xlToLeft))
zsm.Cells(4, "A").Resize(.Rows.Count, .Columns.Count) = .Value
End With
End With
'headers from Bonds
With Worksheets("Bonds")
With .Range(.Cells(4, "B"), .Cells(4, .Columns.Count).End(xlToLeft))
zsm.Cells(4, zsm.Columns.Count).End(xlToLeft).Offset(0, 1).Resize(.Rows.Count, .Columns.Count) = .Value
End With
End With
'ISIN from Bonds
With Worksheets("Bonds")
With .Range(.Cells(5, "A"), .Cells(.Rows.Count, "A").End(xlUp))
zsm.Cells(zsm.Rows.Count, "A").End(xlUp).Offset(1, 0).Resize(.Rows.Count, .Columns.Count) = .Value
End With
End With
'data from Bonds
With Worksheets("Bonds")
With .Range(.Cells(.Rows.Count, "B").End(xlUp), .Cells(5, .Columns.Count).End(xlToLeft))
zsm.Cells(zsm.Cells(zsm.Rows.Count, "B").End(xlUp).Row, _
zsm.Cells(5, zsm.Columns.Count).End(xlToLeft).Column). _
Offset(1, 1).Resize(.Rows.Count, .Columns.Count) = .Value
End With
End With
End Sub
最佳做法"要求您避免选择并提供适当的父级工作表引用.为此,我已经将目标工作表引用作为参数传递给每个"helper"子过程.
'Best practice' dictates that you should avoid Select and provide proper parent worksheet references. To this end, I've passed the target worksheet reference to each 'helper' sub procedure as a parameter.
这篇关于使用PasteSpecial粘贴值而不是公式-VBANewbie的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!