Reference of a sheet

[lezhi]

Hi, I want to specify a sheet with varible i. The reason I used i is that the code below is in a loop, I need to do something on Sheets("1"), Sheets("2") ... to Sheets("36") in turn:

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

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

Thx,

Rachel

 

[AlphaFrog]

If Range("D6") has say the value 10 in it.
So i will equal 10
The macro opens workbook "10.xlsx"
Then you want to insert a row on Sheets(10)

Does the the workbook 10.xlsx have at least 10 worksheets or does it have a sheet named "10" ?

 

[lezhi]

Hi, it does have a sheet with name 10 (I have 36 files to open and each file has a sheet name the same as the file). the complete code is as below:

Sub Mainfunction1()
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

Thank you!
Rachel

 

[AlphaFrog]

Try this...

With Sheets(CStr(i))

If i is an integer say 10, Sheets(i) will reference the 10th sheet in the workbook.

CStr(i) will convert the integer 10 to the text "10" and then reference the Sheet with the text name "10".

 

[Peter_SSs]

Edit: Sorry, AlphaFrog, I think you are on to (one of the) salient points here that I missed in the other thread, but two threads on the same topic is not desirable. Please continue in the other one.

Duplicate to http://www.mrexcel.com/forum/showthread.php?t=572921

Please do not post the same question multiple times. Questions of a duplicate nature will be locked or deleted, per the Posting Rules (especially points 21-23).

Any bumps, clarifications, or follow-ups should be posted back to the original thread.