CODEWITHSUNDEEP
vbaexcelcopy
Bailey
Bailey

Reputation: 15

Paste sheet copied from an open workbook into an existing non open workbook

I am trying to copy "Sheet1" from the workbook I have open, labelled "source.xlsm", and paste after the last sheet of my existing workbook which is not open at the time of running, labelled "target.xlsx".

I have the below code and it seems the whole "C:\" directory does nothing. Is it even possible to put a directory in? I cannot find a way to do this without having the Target.xlsx open.

ActiveSheet.Select
ActiveSheet.Copy After:=Workbooks("C:\Target.xlsx").Sheets("FirstSheet")

Upvotes: 0

Views: 877

Answers (2)

Tim Williams
Tim Williams

Reputation: 166351

You already figured it out, but a suggested edit:

Dim wb As WorkBook
''Open 2nd Workbook
Set wb = Workbooks.Open(Filename:="C:\Archive.xlsx")

''Copy To Different Workbook
Workbooks("Source.xlsx").Sheets("Source").Copy _
         After:=wb.Sheets("Archive")

''Close 2nd Workbook
wb.Save
wb.Close

Upvotes: 1

Bailey
Bailey

Reputation: 15

Answered my own question with the helpful information provided by Tim.

''Open 2nd Workbook
Workbooks.Open Filename:="C:\Archive.xlsx"

''Copy To Different Workbook
Workbooks("Source.xlsx").Sheets("Source").Activate
ActiveSheet.Copy After:=Workbooks("Archive.xlsx").Sheets("Archive")

''Close 2nd Workbook
Workbooks("Archive.xlsx").Sheets("Archive").Activate
ActiveWorkbook.Save
ActiveWorkbook.Close

Upvotes: 0

Related Questions