1

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.

Improve this question

1 Answer 1

Reset to default
2

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!"
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Note that using Set is the only required step to make the original code work, assuming there's a sheet with a CodeName of Sheet6.
Many thanks to Scott and Doug, my problem is solved - Doug your comment meant less change to my existing code, so very helpful. Thankyou both.
Oooops, my elation was a little premature... The sub I use to determine the sheetref variable is seperate to the sub where the sheetref.range("E5").value etc. is used. This seems to be creating a problem.
My guess is if you're returning a string from that function that would be the cause. Seeing the function would help
Yes, it was an error in another part of the script! All working now, thanks again for the assistance.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.