Home Referencing the sheet index in a formula
Reply: 2

Referencing the sheet index in a formula

Richard Hannigan
Richard Hannigan Published in 2018-01-12 15:05:23Z

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"


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


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

braX Reply to 2018-01-12 15:26:30Z

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" 
R. Roe
R. Roe Reply to 2018-01-12 16:17:13Z

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.

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.30189 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO