reference a sheet

[lezhi]

Hi, I want to specify a sheet with varible i:

i = wbkCur.Worksheets("Openwbk").Range("d6")
FileName = "C:\Users\Lele\" & i & ".xlsx"
Set wbkNew = Workbooks.Open(FileName:=FileName)
With Sheets(i)
.Rows("1:1").Insert Shift:=xlDown

It gave the error of "Subscript out of range", at sentence "With Sheets(i)". Please help.

Thx,

Rachel

 

[Marcelo Branco]

Hi Rachel,

Your code uses i to specify the workbook-name to be opened.

So seems not proper to use i to specify a sheet inside the workbook.

Try

With Sheets(1)

or the name of the sheet you want,ie, something like

With Sheets("Sheet1")

HTH

M.

 

[lezhi]

Thank you for the reply. The reason I used i is the code is in a loop, each time, i changes. (from 1 to 36) Is there anyway to implement it?

Rachel

 

[Marcelo Branco]

Are there 36 sheets in the workbook?

If so, maybe using another variable like

Dim j as long
 
For j = 1 to 36
   With Sheets(j)
       'your code here
   End With
Next j

M.

 

[Peter_SSs]

How is the variable 'i' declared in your code? String? Long? etc

Is it correct that the files that you are opening have the same name (i) as a sheet in that workbook?

Guessing a bit, at least one change I think you will need is this

With wbkNew.Sheets(i)

 

[lezhi]

Thank you! Yes, the sheet name is the same as the file name. I tried With wbkNew.Sheets(i), but still got the same error...

 

[Peter_SSs]

Thank you! Yes, the sheet name is the same as the file name. I tried With wbkNew.Sheets(i), but still got the same error...

What about my other question?

Could we see the whole code if it isn't too long?

When you get the error, click Debug and hover over the 'i' variable. What value is it showing?

What value is in D6 where i is being drawn from?

 

[Marcelo Branco]

Thank you! Yes, the sheet name is the same as the file name. I tried With wbkNew.Sheets(i), but still got the same error...

You said you want to loop through 36 sheets - some must have a different name of the workbook-name...

Or do you want to open 36 workbooks all having a sheet with the same same of the workbook?

M.

 

[lezhi]

Hi Peter,
D6 is my input to determine which file to start. (I put 1-36 only) When I get the error, the 'i' variable has value 16, same as what I put in D6.

Below is the code:

Sub Mainfunction()
Dim i As Long
Dim FileName As String
Dim wbkCur As Workbook
Dim wbkNew As Workbook
Set wbkCur = Workbooks("Function book.xlsm")

For i = wbkCur.Worksheets("Openwbk").Range("d6").Value To 36
'might need to change on diff pc
FileName = "C:\Users\Lele\" & i & ".xlsx"
Set wbkNew = Workbooks.Open(FileName:=FileName)
With Sheets(i)
.Rows("1:1").Insert Shift:=xlDown
.Range("A2").Value = MyCount
End With
wbkNew.Close SaveChanges:=True
Next i

End Sub

 

[lezhi]

Yes, I open 36 workbooks one by one. and each has a sheet name the same as the workbook's name.