user362729
user362729

Reputation: 113

Excel assign copied sheet name as variable

I'm trying to copy an existing sheet in my workbook and then I want to use the copied sheet to run the rest of my code. (Sheet3 is the existing sheet, S_Temp is the copied sheet)

Dim s_Temp as string
Sheet3.copy 
Activesheet.name = S_Temp
Sheets("S_Temp").Range("A1").value = "Test"

How can I reference to the copied sheet?

Upvotes: 0

Views: 3297

Answers (2)

user362729
user362729

Reputation: 113

Here's the solution I've just found:

Dim s_Temp as Worksheet
Sheet3.copy   
set S_Temp  = Activesheet  
S_Temp.Range("A1").value = "Test" 

Upvotes: 2

shahkalpesh
shahkalpesh

Reputation: 33484

Sheet3.copy 
'From this point onwards, the active sheet has changed
Activesheet.name = S_Temp

'Hence, this will be A1 on the copied sheet.
Range("A1").value = "Test"

Have I misunderstood the question?

Upvotes: 1

Related Questions