Looping through workseets issue

[cusefan08]

So what I'm trying to accomplish is this:

Run a macro from the "A.xls" that goes into the "B.xls" and copies the B column from here and inserts it into the appropriate sheet in the "A.xls".


I'm getting the 'Selection Method of Worksheet failed' error at the bolded line below.

For i = 1 To 4

a = i + 9
Workbooks("A.xls").Worksheets(i).Select
Columns("B:B").Select
Selection.Copy
Workbooks("B.xls").Worksheets(a).Select
Columns("B:B").Select
Selection.Insert

Next i



Any insight would be very much appreciated! Thanks in advance.

[BrianMH]

try
[VBA]
Workbooks("B.xls").Worksheets(a).activate
[/VBA]

If you give us an example of the spreadsheet and exactly what you are wanting to do we can probably give you much more efficient code for this.

[Wireless Guy]

Try Breaking it into two commands

Workbooks("A.xls").Activate !Assuming you already have it open !somewhere else int he macro
Sheets(i).Select ! I don't think you need worksheet, just sheet
!I don't know the difference

[mancubus]

Try Breaking it into two commands

Workbooks("A.xls").Activate !Assuming you already have it open !somewhere else int he macro
Sheets(i).Select ! I don't think you need worksheet, just sheet
!I don't know the difference

http://www.eggheadcafe.com/tutorials...en-worksh.aspx

If you are working extensively with all the type of sheets (Worksheets, Charts, Modules / Macro, Dialog sheets) and playing with macros or VBA code then it is very important that you know the difference between Worksheets and Sheets Properties in Excel.

Primary difference between these two is Worksheets property identifies only the type "Worksheets" in excel but Sheets is more general and identifies all the types of sheets (Worksheets, Charts, Modules / Macro, Dialog sheets).

To test this and understand the concept clearly, just open a new workbook (default it displays Sheet1, Sheet2 and Sheet3) . Insert a dialog sheet Dialog1, Chart Sheet Chart1 and Macro Sheet Macro1 in the workbook (order of the sheets now is Dialog1, Chart1, Macro1, Sheet1, Sheet2 and Sheet3). Now go to the VB Editor and play around with the below code commenting the necessary lines.

[vba]
Sub TestSheetsWorksheets()

Worksheets(1).Activate 'Activates Sheet1
Worksheets(2).Activate 'Activates Sheet2
Worksheets(3).Activate 'Activates Sheet3
Worksheets(4).Activate 'Throws error "Run Time Error '9': Subscript out of range"
Worksheets(5).Activate 'Throws error "Run Time Error '9': Subscript out of range"
Worksheets(6).Activate 'Throws error "Run Time Error '9': Subscript out of range"

Sheets(1).Activate 'Activates Dialog1
Sheets(2).Activate 'Activates Chart1
Sheets(3).Activate 'Activates Macro1
Sheets(4).Activate 'Activates Sheet1
Sheets(5).Activate 'Activates Sheet2
Sheets(6).Activate 'Activates Sheet3

End Sub
[/vba]
So, next time when you see "Run Time Error '9': Subscript out of range" while you are using Worksheets in the code just take a look at the type of the sheet apart from the number of sheets. You might fix the problem in a second.

[Wireless Guy]

Oh yeah, I knew that...

That's what happens when you post too late at night.

[mdmackillop]

Don't Select

[VBA]For i = 1 To 4
a = i + 9
Workbooks("A.xls").Worksheets(i).Columns("B:B").Copy
Workbooks("B.xls").Worksheets(a).Range("B1").Insert
Next i[/VBA]

[cusefan08]

Thanks all for the help.

What I didn't realize was the Excel workbook had hidden spreadsheets. This made me alter my code to the point it I got the message above.