Archive of Mr Excel Message BoardArchive of Mr Excel Message Board
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
| Check out our Excel VBA Resources | ||||
 |
 |
 |
 |
 |
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.
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