Help with mutliple Worksheets copy and save as

[horizonflame]

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...

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

 

[Fluff]

Try

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

 

[horizonflame]

Hi @Fluff, thanks for the quick reply again. I have two errors when I run the code:

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

Run-time error '13': Type mismatch

This is me not referencing my sheets correctly?

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

Compile error: Expected: name parameter

 

[Fluff]

Try

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

 

[horizonflame]

That is spot on, thanks again.

 

[Fluff]

You're welcome & thanks for the feedback