CODEWITHSUNDEEP
excelvba
The Gootch
The Gootch

Reputation: 85

VBA Set Sheet Error

I'm trying to have 3 spreadsheets combine data by taking all data on Sheet 1 pasting it at the bottom of sheet 2, then taking all data in Sheet 2 and pasting it at the bottom of Sheet 3. The problem is when the macro gets up to set the 'sh' variables, I get a "Run-time error '9': Subscript out of range"

'   Combines all 3 spreadsheets into one

Workbooks.Open "C:\excel\BRCO.CSV"
Workbooks.Open "C:\excel\BRC2.CSV"
Workbooks.Open "C:\excel\BRC3.CSV"

Dim sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet, lr As Long, rng As 
Range

Set sh1 = Sheets("BRCO.csv")
Set sh2 = Sheets("BRC2.csv")
Set sh3 = Sheets("BRC3.csv")
lr = sh1.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = sh1.Range("A1:A" & lr)
rng.EntireRow.Copy sh2.Cells(Rows.Count, 1).End(xlUp)(2)

lr = sh2.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = sh2.Range("A1:A" & lr)
rng.EntireRow.Copy sh3.Cells(Rows.Count, 1).End(xlUp)(2)

Any help would really be appreciated!

Upvotes: 0

Views: 46

Answers (1)

Gary's Student
Gary's Student

Reputation: 96753

Replace:

Set sh1 = Sheets("BRCO.csv")

with:

Set sh1 = Sheets("BRCO")

and place it immediately after the proper Open

Same for the others.

Upvotes: 1

Related Questions