Geek Answers Handbook

Can you copy the worksheet and save the link to the new worksheet?

Can I copy a sheet using VBA and save a link to it? I know that I can do the following in order to maintain a link to a new worksheet, but I need to track the indexes, and it seems that there is already something in it that can do this.

Dim newws as Worksheet
Dim oldws as Worksheet

' set oldws to whatever I need, then
oldws.Copy Before:=Worksheets(1)
Set newws = Worksheets(1)

But it would just be cleaner if something like this were available, as with Worksheets.Add

' You can do this
Set newws = Worksheets.Add    

' But definitely not this, because Sheets.Copy doesn't return anything.
Set newws = oldws.Copy Before:=1

I suppose I can write my own function:

Function CopyAWorksheet(ws As Worksheet, Before As Worksheet) As Worksheet
    ws.Copy Before:=Before
    Set CopyAWorksheet = Worksheets(Before.Index - 1)
End Function

I might add the argument Afterand make options Beforeand Afterso that he acted exactly as I want, but it seems likely that I just missed something that has already been built into the Excel VBA, if there is an interesting technical reasons, it is impossible to I would also like to hear them.

I am using Excel 2010.

+4
1

, , - , , :

oldws.Copy Before:=Sheets(1)
set newws = Activesheet
+2

More articles:


All Articles