CODEWITHSUNDEEP
excelvba
Smartbuild
Smartbuild

Reputation: 119

Excel VB - Refer to worksheet index using a variable?

I would like to use a single line of code to refer to the same cell but in different worksheets (within the same workbook). For example:

Dim SheetRef as Worksheet
SheetRef = Sheet6    
SheetRef.Range("E5").Value = "Please work!"

where SheetRef can be changed within VB to represent different worksheet index numbers (rather than sheet name).

I have tried many combinations, dim'd the variable as many different things, googled high and low and made no progress, any assistance very much appreciated!

Many thanks,

Martin.

Upvotes: 1

Views: 4228

Answers (1)

scott
scott

Reputation: 2275

First, you need to use set to declare your sheet object equal to a sheet. then, use the index notation sheets(i) 

dim sheetref as worksheet
dim i as long

i = 2 'sheet index number

set sheetref = sheets(i)
sheetref.range("E5").value = "WORKS!"

Upvotes: 2

Related Questions