How to Set a Reference to a Copied Worksheet?

[Nick Hebb]

Hi,

I have a section of code that alters a worksheet then exports its
contents to Word. I don't want to roll back the changes, so I just
copy the worksheet, makes the changes, copy to Word, then delete the
copied worksheet.

But the Worksheet.Copy method doesn't return a reference to the new
sheet. So I end up creating a concatenated list of the sheet names
before the Copy, then iterating through sheets after the Copy and
returning the sheet whose name is not in the original list.

It's kind of kludgey and I was wondering whether there was a more
straightforward way. The code looks something like this:

'===============================================================
Private Function CopyWorksheet(OldWorksheet As Worksheet) As Worksheet

Dim ws As Worksheet
Dim sNames As String

If OldWorksheet Is Nothing Then
Set CopyWorksheet = Nothing
Exit Function
End If

sNames = ","
For Each ws In ActiveWorkbook.Worksheets
sNames = sNames & (ws.Name & ",")
Next

OldWorksheet.Copy After:=OldWorksheet

' enclose search string in commas to avoiud substring matching
For Each ws In ActiveWorkbook.Worksheets
If InStr(1, sNames, ("," & ws.Name & ",")) < 1 Then
Set CopyWorksheet = ws
Exit For
End If
Next

'SetPageBreakCounts

End Function
'===============================================================

Sub CopyWorksheet_UnitTest()

Dim ws As Worksheet: Set ws = ActiveSheet

' Test standard usage
Set ws = CopyWorksheet(ws)
If Not ws Is Nothing Then
Debug.Print ws.Name
Else
Debug.Print "<nothing>"
End If

' Test with Nothing
Set ws = Nothing
Set ws = CopyWorksheet(ws)
If Not ws Is Nothing Then
Debug.Print ws.Name
Else
Debug.Print "<nothing>"
End If

Set ws = Nothing

End Sub
'===============================================================

Thanks,

Nicholas Hebb
BreezeTree Software
http://www.breezetree.com

 

[RichardSchollar]

Hi Nick

Use Add to add a sheet and then copy the contents in - eg the
following works for me:

Sub test()
Dim retSh As Worksheet
Set retSh = copysheet(ActiveSheet)
Debug.Print retSh.Name
End Sub
Function copysheet(Sh As Worksheet) As Worksheet
Dim ws As Worksheet
Set ws = Sheets.Add
Sh.Cells.Copy ws.Range("a1")
Set copysheet = ws
End Function

Hope this helps!

Richard

 

[Peter T]

Hi Nick,

A newly inserted sheet always becomes the Activesheet in the workbook, even
if not in the activeworkbook. Though when copying a sheet the workbook also
becomes active.

Alternatively, track the index relative to the 'Before' or 'After' sheet

Sub test2()
Dim wsOrig As Worksheet
Dim wsNew As Worksheet

Set wsOrig = ActiveWorkbook.Worksheets(1)

wsOrig.Copy after:=wsOrig
Set wsNew = ActiveWorkbook.ActiveSheet
Debug.Print wsNew.Name
Set wsNew = ActiveWorkbook.Worksheets(wsOrig.Index + 1)
' or Set wsNew = wsOrig.Parent.Worksheets(wsOrig.Index + 1)
Debug.Print wsNew.Name 'same as above

wsOrig.Copy before:=wsOrig
Debug.Print ActiveWorkbook.Worksheets(wsOrig.Index - 1).Name

' probably best to place a temporary copied sheet at the end
wsOrig.Copy after:=wsOrig.Parent.Worksheets(wsOrig.Parent.Worksheets.Count)
Debug.Print wsOrig.Parent.Worksheets(wsOrig.Parent.Worksheets.Count).Name

' similar to cater for possibility of chart sheets in the wb
wsOrig.Copy after:=wsOrig.Parent.Sheets(wsOrig.Parent.Sheets.Count)
Debug.Print wsOrig.Parent.Sheets(wsOrig.Parent.Sheets.Count).Name

End Sub

Regards,
Peter T

 

[Nick Hebb]

Thanks Richard and Peter.

Richard,

I'm sorry but I should have mentioned that was essentially my original
method. The problem is that you lose all page settings (e.g., paper
size, orientation, print area) when you copy the contents, and those
settings are essential for my export to Word feature.

Peter T,

I guess I've been bitten to many times, so I'm too paranoid to rely on
default behaviors (e.g. ActiveSheet = new sheet) and sheet re-indexing
(e.g., wsOrig.Index + 1). But, I think your last method hits the sweet
spot.


Thanks again,

Nicholas Hebb
BreezeTree Software
http://www.breezetree.com