Copy worksheet to after last worksheet of another workbook

**Giant_Cheeseman** · 05-05-2010, 11:13 PM

Hello, I'm currently working through Excel 2007 Programming VBA for Dummies book, and I'm having trouble with what I want excel to do.
Using the code below, I can get the activesheet name changed but it will only copy to after the FIRST sheet in another workbook.
Am I missing something here? Are there any attributes I should check in the workbook i'm copying to/from?

Sub Macro1()
'
'  Macro1 Macro
'
'  Keyboard Shortcut: Ctrl+Shift+D
'
'  Every macro should have this of course...
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
'  Start main code
    ActiveSheet.Name = Range("H13").Value
    ActiveSheet.Copy After:=Workbooks("APRIL 10.xls").Sheets(Sheets.Count)
'  End main code
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

End Sub

Thanks for your help

btw: This site looks awesome!

**Leith Ross** · 05-05-2010, 11:24 PM

Hello Giant_Cheeseman,

Welcome to the Forum!

To make your code easier to read and more flexible, you can assign object variables to the workbooks.

Sub Macro1()
'
'  Macro1 Macro
'
'  Keyboard Shortcut: Ctrl+Shift+D
'
Dim DstWkb As Workbook

  Set DstWks = Workbooks("APRIL 10.xls")

'  Every macro should have this of course...
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
'  Start main code
    ActiveSheet.Name = Range("H13").Value
    ActiveSheet.Copy After:=DstWkb.Sheets(DstWkb.Sheets.Count)
'  End main code
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

End Sub

**Giant_Cheeseman** · 05-06-2010, 12:40 AM

Originally Posted by Leith Ross

Sub Macro1()
'
'  Macro1 Macro
'
'  Keyboard Shortcut: Ctrl+Shift+D
'
Dim DstWkb As Workbook

  Set DstWks = Workbooks("APRIL 10.xls")

'  Every macro should have this of course...
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
'  Start main code
    ActiveSheet.Name = Range("H13").Value
    ActiveSheet.Copy After:=DstWkb.Sheets(DstWkb.Sheets.Count)
'  End main code
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

End Sub

This code doesn't work. When I run debug, it highlights the line I highlighted above.
The original code I wrote would actually change the name of the sheet at least.
Any help would be appreciated.

**Leith Ross** · 05-06-2010, 12:48 AM

Hello Giant_Cheeseman,

That's my fault due to a typo. I corrected the typo in this code.

Sub Macro1()
'
'  Macro1 Macro
'
'  Keyboard Shortcut: Ctrl+Shift+D
'
Dim DstWkb As Workbook

  Set DstWkb = Workbooks("APRIL 10.xls")

'  Every macro should have this of course...
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
'  Start main code
    ActiveSheet.Name = Range("H13").Value
    ActiveSheet.Copy After:=DstWkb.Sheets(DstWkb.Sheets.Count)
'  End main code
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

End Sub

**Giant_Cheeseman** · 05-06-2010, 01:01 AM

Ah, yes that worked. However only after I realised my macro key stopped working. I have reset it in macro options, and the code worked.

Thanks!

**Giant_Cheeseman** · 05-06-2010, 02:56 AM

Just a quick question, could this code have an attachment where in the same process, it could save the workbook as the value defined (H13)?

Or potentially save the sheet as a workbook with the sheet name (might be easier)?