MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Return to Original Workbook

Posted by Alaina on December 13, 2001 7:39 AM

I have written a macro that copies the sheets in one workbook to other open workbooks. Each time through though, I want to see the original workbook and the sheet being copied. Any way to do this? Thanks :-)

Posted by bob Umlas on December 13, 2001 7:41 AM

You can set references before changing to another workbook:
Set Orig = activeworkbook
...'some code here
Set SheetBeingCopied = ActiveSheet
...'more code
Orig.Activate will show original
SheetBeingCopied.Parent.Activate activates the wb containing the sheet, then SheetBeingCopied.Activate will activate it.

Posted by Alaina on December 13, 2001 8:36 AM

The following is returning me to the original workbook, but not showing me that sheet being copied. The workbook has various sheets and I want to see what sheet is being copied each time. What am I doing wrong:

Sub Copy_Qtr()
Application.ScreenUpdating = True
Set Orig = ActiveWorkbook
Dim Sh As Worksheet
For Each Sh In Worksheets
Set SheetBeingCopied = ActiveSheet
ActiveSheet.Name = "QTD"
End Sub