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


On 19 Feb, 05:50, "Nick Hebb" wrote:
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 Softwarehttp://www.breezetree.com