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.
HTH

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
SheetBeingCopied.Parent.Activate
Application.Dialogs(xlDialogWorkbookCopy).Show
ActiveSheet.Name = "QTD"
Orig.Activate
Next
End Sub