VBA Copy Worksheet to Another Workbook

Small Paul

Board Regular
Joined
Jun 28, 2018
Messages
118
Hi

I have 2 workbooks. Workbook A contains 1 worksheet. Workbook B (purchased items) has a variable number of worksheets.

I need to copy the worksheet in A and add it to be the LAST worksheet in B.

The code I have at present is:



Code:
Sub Macro1()'
' Macro1 Macro
'
activesheet.name = "Payee Details"
activesheet.Copy
after = Workbooks("Purchased Items.xlsm").worksheets.Count


End Sub
When run, the name is changed and the worksheet is copied.

However, it does not appear in 'Purchased Items'

I know it is something simple but can somebody please help?

Cheers
Small paul.
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
36,012
Office Version
365
Platform
Windows
If the code is in Workbook A try
Code:
Sub SmallPaul()
   Dim Wbk As Workbook
   
   Set Wbk = Workbooks("Purchased Items.xlsm")
   With ThisWorkbook.Sheets(1)
      .Name = "Payee details"
      .Copy , Wbk.Sheets(Wbk.Sheets.Count)
   End With
End Sub
 

Small Paul

Board Regular
Joined
Jun 28, 2018
Messages
118
I keep ALL my code in 'Personal.xlsx'
The above did not work, even when adjusted for the location.
I now have the following which fails on the final line "Run-time error 1004: Copy method of Worksheet class failed

Code:
Sub Macro3()'
' Macro3 Macro
'
   ActiveWorkbook.SaveAs "Z:\Paul\Payee Details.xls"
       activesheet.name = "Payee Details"
       worksheets("Payee Details").Select
   Workbooks("Payee Details.xls").worksheets("Payee Details").Copy , after = Workbooks("Purchased Items.xlsm").worksheets.Count


End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
36,012
Office Version
365
Platform
Windows
In that case, change ThisWorkbook to
Code:
Workbooks("Aname.xlsm")
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,090,247
Messages
5,413,291
Members
403,472
Latest member
ArtisticOwl

This Week's Hot Topics

Top