Run Time Error '438' Copy Multiple Columns

[Dokat]

Hi,

I have below code i am trying to use to copy multiple columns from one workbook to another. However i am getting Run Time Error. Can someone help me understand whats causing the problem? Thanks

Sub CopyColumnToWorkbook()

OptimizeVBA True

Dim source As Workbook
Dim Target As Worksheet
Dim Sheet As Worksheet

Set source = Workbooks.Open("G:\USNSH_DG\Reports\Segmentation\Weekly Snapshot Report.xlsx")
Set Target = ThisWorkbook.Worksheets("Source")
Set Sheet = Worksheets("Grid")

Application.ScreenUpdating = False

With source.Sheet
.Columns("A3:A & lastrow").Copy Destination:=Target.Columns("B")
.Columns("O3:O & lastrow").Copy Destination:=Target.Columns("D")
.Columns("P3:P & lastrow").Copy Destination:=Target.Columns("E")
.Columns("L3:L & lastrow").Copy Destination:=Target.Columns("S")
.Columns("M3:M & lastrow").Copy Destination:=Target.Columns("T")
.Columns("I3:I & lastrow").Copy Destination:=Target.Columns("AH")
.Columns("J3:J & lastrow").Copy Destination:=Target.Columns("AI")
.Columns("F3:F & lastrow").Copy Destination:=Target.Columns("AW")
.Columns("G3:G & lastrow").Copy Destination:=Target.Columns("AX")
.Columns("C3:C & lastrow").Copy Destination:=Target.Columns("BL")
.Columns("D3:D & lastrow").Copy Destination:=Target.Columns("BM")


OptimizeVBA False

source.Close SaveChanges:=False

Set source = Nothing
Set Target = Nothing
Set Sheet = Nothing

Application.ScreenUpdating = True
End With
End Sub

 

[Chihiro]

From what I can see. You haven't defined lastrow variable.

Also syntax for range to copy is off. Should be...

.Range("A3:A" & lastrow)

Instead of...

.Columns("A3:A & lastrow")

Another issue is that your destination should not be entire column but top left most cell that copied range should be pasted to...
Ex: Assuming Row 1 contains header.

Destination:=Target.Range("B2")

FYI - When you paste to entire column, values will be repeated to fill entire column, instead of pasting value once.

 

[Dokat]

Thanks for your response.

I adjusted the code however still getting the same error message. Please see below updated code.

Sub CopyColumnToWorkbook()

OptimizeVBA True

Dim source As Workbook
Dim Target As Worksheet
Dim Sheet As Worksheet
Dim lRow As Long

Set source = Workbooks.Open("G:\USNSH_DG\Reports\Segmentation\Weekly Snapshot Report.xlsx")
Set Target = ThisWorkbook.Worksheets("Source")
Set Sheet = Worksheets("Grid")

lrow = Sheet.Cells(Rows.Count, 3).End(xlUp).Row

Application.ScreenUpdating = False

With source.Sheet
.Range("A3:A" & lrow).Copy Destination:=Target.Range("B3")
.Range("O3:O" & lrow).Copy Destination:=Target.Range("D3")
.Range("P3:P" & lrow).Copy Destination:=Target.Range("E3")
.Range("L3:L" & lrow).Copy Destination:=Target.Range("S3")
.Range("M3:M" & lrow).Copy Destination:=Target.Range("T3")
.Range("I3:I" & lrow).Copy Destination:=Target.Range("AH3")
.Range("J3:J" & lrow).Copy Destination:=Target.Range("AI3")
.Range("F3:F" & lrow).Copy Destination:=Target.Range("AW3")
.Range("G3:G" & lrow).Copy Destination:=Target.Range("AX3")
.Range("C3:C" & lrow).Copy Destination:=Target.Range("BL3")
.Range("D3:D" & lrow).Copy Destination:=Target.Range("BM3")


OptimizeVBA False

source.Close SaveChanges:=False

Set source = Nothing
Set Target = Nothing
Set Sheet = Nothing

Application.ScreenUpdating = True
End With
End Sub

 

[Chihiro]

Which line is highlighted when you go into debug mode?

Edit: One thing I noticed, you haven't qualified Sheet with workbook.
I'd set it like below.

Set Sheet = source.Worksheets("Grid")

 

[Dokat]

Below line is highlighted

With source.Sheet

 

[Chihiro]

Set sheet as I mentioned and just use...

With Sheet

 

[Dokat]

It worked! Thank you