Copy and paste in multiple columns with VBA I need copy different columns from w

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

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.