Excel VBA - copy multiple columns from 2 workbooks into 1 workbook

Question

I have two workbooks that have data in multiple columns that I want copied and put into a new workbook that contains this macro. Basically I want to be able to open the current workbook, "z", run the macro, and have the data move/copy over from the other two existing workbooks.

I have been searching around here and looking at different ideas from different posts, and think I have pretty much figured this out but am running into a "Run-time error 1004" when I run this code. The error happens at the first y.Range line, and I have yet to figure out why.

I have successfully been able to pull the data from "x" workbook but not "y".

I am new to VBA so any help would be greatly appreciated.

Thanks,

Sub SellPrice()

Dim x As Worksheet, y As Worksheet, z As Worksheet, LastRow&

Workbooks.Open ("C:\Users\tsmith\Desktop\SellPrice\PRODUCT.XLS")
Workbooks.Open ("C:\Users\tsmith\Desktop\SellPrice\GrossProfit.xls")

Set x = Workbooks("PRODUCT.XLS").Worksheets("ProductFile")
Set y = Workbooks("GrossProfit.xls").Worksheets("Sellprice")
Set z = Workbooks("SellPriceMacro.xlsm").Worksheets("Sheet1")

LastRow = x.Cells.SpecialCells(xlCellTypeLastCell).Row

x.Range("B4:B" & LastRow).Copy z.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
x.Range("C4:C" & LastRow).Copy z.Cells(Rows.Count, "B").End(xlUp).Offset(1, 0)
x.Range("K4:K" & LastRow).Copy z.Cells(Rows.Count, "C").End(xlUp).Offset(1, 0)

LastRow = y.Cells.SpecialCells(xlCellTypeLastCell).Row

y.Range("B2:B" & LastRow).Copy z.Cells(Rows.Count, "E").End(x1Up).Offset(1, 0)
y.Range("C2:C" & LastRow).Copy z.Cells(Rows.Count, "F").End(x1Up).Offset(1, 0)
y.Range("D2:D" & LastRow).Copy z.Cells(Rows.Count, "G").End(x1Up).Offset(1, 0)
y.Range("H2:H" & LastRow).Copy z.Cells(Rows.Count, "H").End(x1Up).Offset(1, 0)

Application.CutCopyMode = False

End Sub

BruceWayne BruceWayne · Accepted Answer · 2016-10-20T20:16:51

See how you put the worksheet before you used Range()? (y.Range(...)) - you need to do that any time you use Cells(), Rows.Count, and Columns.Count (and other ranges). Otherwise, it's going to look to whatever the ActiveSheet is, and use that. When you mix inactive sheets with active sheets, you'll get an error.

Sub SellPrice()

Dim x As Worksheet, y As Worksheet, z As Worksheet, LastRow&

Workbooks.Open ("C:\Users\tsmith\Desktop\SellPrice\PRODUCT.XLS")
Workbooks.Open ("C:\Users\tsmith\Desktop\SellPrice\GrossProfit.xls")

Set x = Workbooks("PRODUCT.XLS").Worksheets("ProductFile")
Set y = Workbooks("GrossProfit.xls").Worksheets("Sellprice")
Set z = Workbooks("SellPriceMacro.xlsm").Worksheets("Sheet1")

LastRow = x.Cells.SpecialCells(xlCellTypeLastCell).Row

x.Range("B4:B" & LastRow).Copy z.Cells(z.Rows.Count, "A").End(xlUp).Offset(1, 0)
x.Range("C4:C" & LastRow).Copy z.Cells(z.Rows.Count, "B").End(xlUp).Offset(1, 0)
x.Range("K4:K" & LastRow).Copy z.Cells(z.Rows.Count, "C").End(xlUp).Offset(1, 0)

LastRow = y.Cells.SpecialCells(xlCellTypeLastCell).Row

y.Range("B2:B" & LastRow).Copy z.Cells(z.Rows.Count, "E").End(xlUp).Offset(1, 0)
y.Range("C2:C" & LastRow).Copy z.Cells(z.Rows.Count, "F").End(xlUp).Offset(1, 0)
y.Range("D2:D" & LastRow).Copy z.Cells(z.Rows.Count, "G").End(xlUp).Offset(1, 0)
y.Range("H2:H" & LastRow).Copy z.Cells(z.Rows.Count, "H").End(xlUp).Offset(1, 0)

Application.CutCopyMode = False

End Sub

Excel VBA - copy multiple columns from 2 workbooks into 1 workbook

1 Answers