Working with multiple workbooks

[CT56]

I am trying to run a macro in one work book that references a second. I have the following code but with the sheets I want to use it returns an error 9 - subscript out of range.

However, if I run the same code on an empty workbook it seems to work. ThisWorkbook has about 65,000 rows, and the account master 24,941. Is there a limit to the number of rows that I can access using this method? Is there an alternative? I don't really want to cut and past the data into a single workbook as this is for a report I will need to run on a regular basis.

Dim wbBook1 As Workbook
Dim wbBook2 As Workbook
Dim wsSheet1 As Worksheet
Dim wsSheet2 As Worksheet
Dim LastrowLiveAccountMaster As Long

Set wbBook1 = ThisWorkbook
Set wbBook2 = Workbooks.Open("C:\Users\TaylorC\Excel Project\Live Account Master Sheet.xlsm")
Set wsSheet1 = wbBook1.Worksheets("Sheet1")
Set wsSheet2 = wbBook2.Worksheets("Sheet1")

Regards
CT

 

[DanteAmor]

Set wsSheet1 = wbBook1.Worksheets("Sheet1")
Set wsSheet2 = wbBook2.Worksheets("Sheet1")

Check that Sheet1 exists in both books (without spaces on the right or left side)


And yes you can handle that amount of registrations.

 

[HighAndWilder]

I am trying to run a macro in one work book that references a second. I have the following code but with the sheets I want to use it returns an error 9 - subscript out of range.

However, if I run the same code on an empty workbook it seems to work. ThisWorkbook has about 65,000 rows, and the account master 24,941. Is there a limit to the number of rows that I can access using this method? Is there an alternative? I don't really want to cut and past the data into a single workbook as this is for a report I will need to run on a regular basis.

Dim wbBook1 As Workbook
Dim wbBook2 As Workbook
Dim wsSheet1 As Worksheet
Dim wsSheet2 As Worksheet
Dim LastrowLiveAccountMaster As Long

Set wbBook1 = ThisWorkbook
Set wbBook2 = Workbooks.Open("C:\Users\TaylorC\Excel Project\Live Account Master Sheet.xlsm")
Set wsSheet1 = wbBook1.Worksheets("Sheet1")
Set wsSheet2 = wbBook2.Worksheets("Sheet1")

Regards
CT

Which line of code do you get the error on?

 

[CT56]

Check that Sheet1 exists in both books (without spaces on the right or left side)


And yes you can handle that amount of registrations.

Thanks. This seems to be the answer. I had assumed (wrongly!) that this referenced the index number and not the name. DoH!

 

[CT56]

Which line of code do you get the error on?

Thanks for replying. My mistake was assuming that the Sheet1 referenced the index number and not the actual name. For some reason Sheet1 had a space (Sheet 1). These are not my spreadsheets and did not notice somebody had altered the name!