Copy one tab from a sheet to another workbook

0
votes

I'm writing a macro to copy tabs tabs of an active workbook to different workbooks. So basically what I want to do is:

  • Press button
  • Copy tab named "1."
  • Open other workbook named "1.xlsx"
  • Paste the tab as last
  • Rename it

I try to do it with the following code:

Sub Copy()

'Open the sheet in which I want to copy the tab

Workbooks.Open ("C:\Users\aa471714\Desktop\RTS\1.xlsx")

'Copy the sheet from an activeworkbook to the tab after the excelsheet i just opened (1.xlsx)

ActiveWorkbook.Sheets("3.").Copy _
after:=Workbooks("1.xlsx").Sheets("1.")

'Define new sheetname
shtname = InputBox("What's the new sheet name?", "Sheet name?")
ActiveSheet.Name = "3_" & shtname

End Sub

But I get a fault 9. Any thoughts on a quick fix?

Regards, Marc

1
excelvba
When you open 1.xlsx it becomes the active workbook. If the code is in the workbook you are copying the sheet from, refer to ThisWorkbook instead. – Rory

1 Answers

0
votes

There may be a problem with ActiveWorkbook

Does this help?

Sub Copy()
    Dim source As Workbook, sht As Worksheet
    Set source = ActiveWorkbook
    Set sht = source.Sheets("3.")

'Open the sheet in which I want to copy the tab

    Workbooks.Open ("C:\Users\aa471714\Desktop\RTS\1.xlsx")

'Copy the sheet from original to the tab after the excelsheet i just opened (1.xlsx)

    sht.Copy after:=ActiveWorkbook.Sheets("1.")

'Define new sheetname

    shtname = InputBox("What's the new sheet name?", "Sheet name?")
    ActiveSheet.Name = "3_" & shtname

End Sub