Help with mutliple Worksheets copy and save as

horizonflame

Board Regular
Joined
Sep 27, 2018
Messages
186
Office Version
  1. 2013
Hi All,

Last question to finish my recent project :D

I need to perform the following functions:

- Copy Sheet2, Sheet3 and Sheet 4 to new workbook (using codename as sheet name change weekly)
- Hide Sheet2
- Save workbook in original directory taking Sheet2 name and fixed suffix
- Remain/return to original workbook

This is what I have tried to put together so far...

Code:
Application.ScreenUpdating = False


Sheets(Array(Sheet2, Sheet3, Sheet4)).Move

Sheet2.Visible = False

ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.Path & "\" & Sheet2.Name & "_Error_Corrections" & ".xlsx"


Application.ScreenUpdating = True

Thank you :)
 
Last edited:

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Try
Code:
ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & Sheet2.Name & "_Error_Corrections" & ".xlsx",51
 
Upvote 0
Hi @Fluff, thanks for the quick reply again. I have two errors when I run the code:

Code:
Sheets(Array(Sheet2, Sheet3, Sheet4)).Move

Run-time error '13': Type mismatch

This is me not referencing my sheets correctly?

Code:
ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & Sheet2.Name & "_Error_Corrections" & ".xlsx",51

Compile error: Expected: name parameter
 
Upvote 0
Try
Code:
Sub horizonflame()
   Dim Fname As String
   Fname = Sheet2.Name
   Sheets(Array(Sheet2.Name, Sheet3.Name, Sheet4.Name)).Move
   Sheets(Fname).Visible = False
   ActiveWorkbook.SaveAs ThisWorkbook.path & "\" & Fname & "_Error_Corrections" & ".xlsx", 51
End Sub
 
Last edited:
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top