Referencing the sheet index in a formula

2
votes

Hopefully someone can help i am trying to reference the sheetcode rather than the sheet name as the sheet name changes depending on the workbook i am referencing.

my current code that i have been using is this 

'Control Card 3 extracting from a closed workbook in to my active sheet`

Range("C1196:C1219").Formula = "='[ASE Template White Book.xlsx]Cntrl 3'!$C6"
Range("D1196:D1219").Formula = "='[ASE Template White Book.xlsx]Cntrl 3'!$D6"
Range("C1220:C1243").Formula = "='[ASE Template White Book.xlsx]Cntrl 3'!$C38"
Range("D1220:D1243").Formula = "='[ASE Template White Book.xlsx]Cntrl 3'!$D38"

This works fine but i am trying to use 

    'Control Card 3 
Range("C1196:C1219").Formula = "='[ASE Template White Book.xlsx]Sheet (30)'!$C6"

or

Range("D1196:D1219").Formula = "='[ASE Template White Book.xlsx]Sheet 30'!$D6"

or

Range("C1220:C1243").Formula = "='[ASE Template White Book.xlsx].Sheet30'!$C38"

I am trying to extract the value of cells to my worksheet i am working on but as mentioned the sheet name will change depending on the closed workbook which changes as the original document is saved as ASE template white book.xlsx so i have a constant reference document for the code.

any help aprreciated

2
excelvba
Did you try this way? Range("C1196:C1219").Formula = "='[ASE Template White Book.xlsx]" & Sheet30.Name & "'!$C6" - Subodh Tiwari sktneer

2 Answers

0
votes

You will want to refer to the Worksheet's index to get its Name and use that as a variable in your formula like this:

Range("C1196:C1219").Formula = "='[ASE Template White Book.xlsx]" & Worksheets(30).Name & "'!$C6"
0
votes

Based on this site http://www.ozgrid.com/VBA/excel-vba-sheet-names.htm

"The only times you cannot use a sheets CodeName is when you reference a Sheet that is in a different Workbook to the one that the code resides"

So if you are trying to get the sheet codename of a closed workbook it will not work. You can only use the sheet codename of the workbook that has the macro you are running. The only other way you can reference a sheet is by doing something that @braX showed in their example, by getting the sheet index and referencing the sheet by it's index.