Activate sheet being copied

Posted by Alaina on December 13, 2001 12:46 PM

The following is returning me to the original workbook, but not showing me what sheet is 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

Posted by Juan Pablo G. on December 13, 2001 3:57 PM

How about:

Sub Copy_Qtr()
Dim Orig as Workbook
Dim Sh as Worksheet
Set Orig = ActiveWorkbook
For Each Sh In Orig.Worksheets
Sh.Activate
Application.Dialogs(xlDialogWorkbookCopy).Show
ActiveSheet.Name = "QTD"
Orig.Activate
Next
End Sub

That's a strange macro you've got there... are you specifing manually which workbook to copy ?

Juan Pablo G.

Posted by Alaina on December 14, 2001 7:40 AM

Thanks Juan Pablo, this works great.

I have a workbook with 15 sheets and need to copy each worksheet to a different open workbook. If I can't see what's being copied, I lose track of to which workbook it should be copied.

I'm selecting the open workbook manually because I couldn't figure out another way. I was hoping I could search the open workbook names for the sheet name.

i.e. First sheet name is "100" and the workbook it needs to be copied to is "Nov_100." Second sheet name is "110" and it needs to be copied to "Nov_110.xls" etc.

I don't want to hardcode the file name because it changes each month. Dec will replace Nov for the next run.

Do you know of some better way to do this? Thanks :-) Alaina