Help with mutliple Worksheets copy and save as

horizonflame

Board Regular
Joined
Sep 27, 2018
Messages
171
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:

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
39,164
Office Version
365
Platform
Windows
Try
Code:
ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & Sheet2.Name & "_Error_Corrections" & ".xlsx",51
 

horizonflame

Board Regular
Joined
Sep 27, 2018
Messages
171
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
39,164
Office Version
365
Platform
Windows
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:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
39,164
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,096,444
Messages
5,450,485
Members
405,613
Latest member
Arpit

This Week's Hot Topics

Top