Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Set a Reference to a Copied Worksheet?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Set a Reference to a Copied Worksheet?
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Set a Reference to a Copied Worksheet?
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 befo=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.Work sheets.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.C ount) Debug.Print wsOrig.Parent.Sheets(wsOrig.Parent.Sheets.Count).N ame End Sub Regards, Peter T "Nick Hebb" wrote in message ups.com... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Set a Reference to a Copied Worksheet?
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
anchoring a cell reference in formula copied or dragged over a ran | Excel Discussion (Misc queries) | |||
how to change old data source reference from copied pivot tables | Excel Discussion (Misc queries) | |||
How do I get a worksheet reference to increment when copied | Excel Worksheet Functions | |||
XL2007: Linked-labels reference is not copied with sheet | Charts and Charting in Excel | |||
Cell reference update when copied and pasted | Excel Worksheet Functions |