Copy and paste in multiple columns with VBA I need copy different columns from w
ID: 637982 • Letter: C
Question
Copy and paste in multiple columns with VBA
I need copy different columns from worksheet and paste in columns from other worksheeet.
I have this code but when make a paste mark an error in the line of paste.
Set a = Range("A1", Range("A1").End(xlDown))
Set b = Range("B1", Range("B1").End(xlDown))
Set c = Range("W1", Range("W1").End(xlDown))
Set d = Range("F1", Range("F1").End(xlDown))
Set e = Range("S1", Range("S1").End(xlDown))
Set f = Range("AW1", Range("B1").End(xlDown))
Set g = Range("BC1", Range("B1").End(xlDown))
Union(a, b, c, d, e, f, g).Select
Selection.Copy
Dim MyFile As String
MyFile = Application.GetOpenFilename()
Workbooks.Open (MyFile)
Sheets("Sheet1").Select
Set a = Range("C2", Range("C2").End(xlDown))
Set b = Range("D2", Range("D2").End(xlDown))
Set c = Range("E2", Range("E2").End(xlDown))
Set d = Range("F2", Range("F2").End(xlDown))
Set e = Range("H2", Range("H2").End(xlDown))
Set f = Range("L2", Range("L2").End(xlDown))
Set g = Range("N2", Range("N2").End(xlDown))
Union(a, b, c, d, e, f, g).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False '<== Error in this line
Application.CutCopyMode = False
Thanks for help !!
Explanation / Answer
Hi..
You cannot paste that way - do it this way, column by column....
Sub TestMacro()
Dim wSrc As Workbook
Dim sSrc As Worksheet
Dim wDest As Workbook
Dim sDest As Worksheet
Dim MyFile As String
MyFile = Application.GetOpenFilename().
Set wSrc = ActiveWorkbook
Set sSrc = ActiveSheet
Set wDest = Workbooks.Open(MyFile)
Set sDest = wDest.Worksheets("Sheet1")
sDest.Range("C2", sDest.Range("C2").End(xlDown)).Clear
sSrc.Range("A1", sSrc.Range("A1").End(xlDown)).Copy.
sDest.Range("C2").PasteSpecial xlPasteValuess
'This is the code block to copy and paste a column.
sDest.Range("D2", sDest.Range("D2").End(xlDown)).Clear
sSrc.Range("B1", sSrc.Range("B1").End(xlDown)).Copy
sDest.Range("D2").PasteSpecial xlPasteValues
'Repeat above code block for each column
End Sub