Workboos Object : Subscript out of range

[Apple1]

Hi

I get the error Subscript out of range in the below code. What is wrong with it? Thank you




Option Explicit

Sub test()


Dim i As Integer



For i = 1 To 6
ThisWorkbook.Worksheets(1).Cells(i, 1).Value = Workbooks(2).Worksheets(i).Name


Next

End Sub

 

[AlphaFrog]

Does Workbooks(2) have 6 worksheets?

Does it list the names of any sheets before it errors?

 

[Apple1]

Hi

Thank you. Yes Workbooks(2) have 6 shsets


QUOTE=AlphaFrog;4929414]Does Workbooks(2) have 6 worksheets?

Does it list the names of any sheets before it errors?[/QUOTE]

 

[MARK858]

... and the 2nd Question Alphafrog asked?

 

[Anthony47]

My guess is that you misinterpret which is Workbook(2).
Add this line in this position:

For i = 1 To 6
Debug.Print Workbooks(2).Name               '<<< ADD THIS LINE
ThisWorkbook.Worksheets(1).Cells(i, 1).Value = Workbooks(2).Worksheets(i).Name
Next

Then, while in vba debug mode, open the "Immediate" window (press Contr-g), anche check is the printed name is "your" Workbooks(2)

Bye

 

[AlphaFrog]

Try this...

[color=darkblue]Sub[/color] test()
    
    [color=darkblue]Dim[/color] i [color=darkblue]As[/color] [color=darkblue]Integer[/color]
    
    [color=darkblue]For[/color] i = 1 [color=darkblue]To[/color] [B]Workbooks(2).Worksheets.Count[/B]
        ThisWorkbook.Worksheets(1).Cells(i, 1).Value = Workbooks(2).Worksheets(i).Name
    [color=darkblue]Next[/color]
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]