CODEWITHSUNDEEP
excelvbacopypaste
VeVi
VeVi

Reputation: 281

copy sheet to another workbook but the sheet is also saved in a new workbook

I need to copy a sheet from one workbook to another (existing) workbook. It works but the sheet is also automatically saved in a new workbook. I have no idea why this is. this is my code:

Worksheets("New").Activate
If IsSheetEmpty(StartWorkbook.Sheets("New")) = True Then
    Application.DisplayAlerts = False
    Worksheets("New").Delete
    OLDWorkbook.Activate
    Sheets("sheet1").Select
    Sheets("sheet1").Copy
    Sheets("sheet1").Copy After:=StartWorkbook.Sheets("Control")
    StartWorkbook.Activate
    Sheets("sheet1").Name = "New"
    OLD.Close
    Application.DisplayAlerts = True   
Else
    MsgBox ("sheet is not empty")
End If

Thanks in advance

Upvotes: 0

Views: 232

Answers (1)

Dirk Reichel
Dirk Reichel

Reputation: 7979

Just to have a proper answer:

This behavior is due to the fact that copying a sheet without a destination, a new workbook will be created holding this sheet. For this reason there is no command to "paste" a copied sheet.

For the given code, just remove the line

Sheets("sheet1").Copy

But keep the line:

Sheets("sheet1").Copy After:=StartWorkbook.Sheets("Control")

(obviously, it is needed) ;)

Upvotes: 1

Related Questions