Can't copy cells from Worksheet A in Workbook 1 to Worksheet B in Workbook 2

Originally Posted by exartizo

Any help is appreciated

I am trying to create an inventory tracking system. I have a master workbook with individual worksheets for each department and a master worksheet for all department information.

I need to write VB for Excel that copies all of the source data from a department's own spreadsheet into the master workboook department spreadsheet where the format can be manipulated and then placed in the master worksheet of all department information. The VB should auto check for the last row in the source worksheet and update information when the macro is run in the master inventory workbook for each department worksheet.

This is what I have so far:

Sub WorksheetCopy()


' I also need to write a loop method of detecting end of data in worksheet, check for row etc.

For R = 2 To 8
For C = 1 To 18
With Worksheets("test1").Cells(R, C)
If .Value > 0.001 Then Worksheets("test1").Cells(R, C).Copy _
Destination:=Worksheets("test2").Cells(R, C) End With
Next C
Next R

End Sub

The problem is that (in red above) I get an error message:

Run-time error '9':
Subscript out of range

when I try to link the source to a destination workbook/worksheet outside of the current workbook. I can copy data fine while the data is in the same workbook, but I get the error without exception when I try to copy the data to a worksheet in another workbook. These files will be used over the network and so the path is rather long for the actual files.

thanks

Your code is accurate assuming you have two worksheets in the same workbook, one named test1 and the other test2.

If you're using more than one workbook you will want to fully qualify your destination as in:

Destination := Workbooks(2).Worksheets("test2").Cells(R, C)

This assumes your ActiveWorkbook is 1 and the one you're writing to is 2.

Look at the following Microsoft article for determining the last cell in your worksheet:

http://msdn.microsoft.com/library/de...ml/web/004.asp