I am trying to find a macro that will copy a sheet from my original workbook to a new workbook (doesn't need to be saved or named at this point) and then go back to my original workbook and delete that sheet plus some more actions in the original workbook like hiding sheets.
What needs to be left is the original workbook open and the newly created workbook with the sheet available for editing as required.
I have got so far but as soon as the new workbook is created the macro stops and fails to follow the rest of the instructions to go back to the original workbook and delete the sheet and hide the sheet.
Any help appreciated. .
Sub Mymacro()
Dim w As Workbook, x As Workbook
Set w = ThisWorkbook
Application.ScreenUpdating = False
'copy sheet to a new workbook
Sheets("PRNT2").Copy
Set x = ActiveWorkbook
'go back to original workbook
ThisWorkbook.Activate
'delete "PRNT2" sheet
Sheets("PRNT2").Select
ActiveWindow.SelectedSheets.Delete
'Hide "PRNT1" sheet
Sheets("PRNT1").Hide
End Sub
What needs to be left is the original workbook open and the newly created workbook with the sheet available for editing as required.
I have got so far but as soon as the new workbook is created the macro stops and fails to follow the rest of the instructions to go back to the original workbook and delete the sheet and hide the sheet.
Any help appreciated. .
Sub Mymacro()
Dim w As Workbook, x As Workbook
Set w = ThisWorkbook
Application.ScreenUpdating = False
'copy sheet to a new workbook
Sheets("PRNT2").Copy
Set x = ActiveWorkbook
'go back to original workbook
ThisWorkbook.Activate
'delete "PRNT2" sheet
Sheets("PRNT2").Select
ActiveWindow.SelectedSheets.Delete
'Hide "PRNT1" sheet
Sheets("PRNT1").Hide
End Sub