Moving and saving sheets between workbooks using Code Name & Sheet Name

[horizonflame]

Hi

I have a workbook with several sheets that I need to move into into new seperate workbooks. The sheet names change each week but the sheet code name remains the same (Sheets 4-8). The new workbooks will be saved in the same folder and take the name of the sheet.

I have found the following code from another thread which I think may be useful:

ActiveSheet.Move

With ActiveWorkbook
.SaveAs Filename:=ThisWorkbook.Path & "" & .Sheets(1).Name & ".xls"
.Close savechanges:=False

End With
Could you help me with how I can refer to the code name to select the right sheet in the code and then take the sheet name as the workbook name?

Thanks

 

[Fluff]

How about

Sub horizonflame()
   Dim Ary As Variant
   Dim i As Long
   
   Ary = Array(Sheet4, Sheet5, Sheet6, Sheet7, Sheet8)
   For i = 0 To UBound(Ary)
      Ary(i).Copy
      With ActiveWorkbook
         .SaveAs ThisWorkbook.path & "\" & Ary(i).name, 51
         .Close
      End With
   Next i
End Sub

 

[Fluff]

Apologies the code should be

Sub horizonflame()
   Dim Ary As Variant
   Dim i As Long
   
   Ary = Array(Sheet4, Sheet5, Sheet6, Sheet7, Sheet8)
   For i = 0 To UBound(Ary)
      Ary(i).[COLOR=#ff0000]Move[/COLOR]
      With ActiveWorkbook
         .SaveAs ThisWorkbook.path & "\" & Ary(i).name, 51
         .Close
      End With
   Next i
End Sub

 

[horizonflame]

That's brilliant, thank you

 

[Fluff]

You're welcome & thanks for the feedback

 

[horizonflame]

Hi again @Fluff,

I'm a getting a 424 error on this line:

 .SaveAs ThisWorkbook.Path & "\" & Ary(i).Name, 51

Your help again would be appreciated

 

[Fluff]

What is the value of Ary(i).Name when it fails?
Also what is the error message?

 

[horizonflame]

Hi @Fluff, Thanksfor the reply. The error is: “Run-time error ‘424’: Object required”

I'm not sure about your first question?

The code is breaking on:

'.SaveAsThisWorkbook.Path & "" & Ary(i).Name, 51

My up to date code I’m using is:

 Sub horizonflame2()
DimAry As Variant

[FONT=Calibri][SIZE=3][COLOR=#000000]Dim i As Long[/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000]  Ary = Array(Sheet6, Sheet7, Sheet8, Sheet9,Sheet10, Sheet11, Sheet12, Sheet13, Sheet14, Sheet15, Sheet16, Sheet17,Sheet18, Sheet19, Sheet20, Sheet21, Sheet22)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]   For i = 0 To UBound(Ary)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]      Ary(i).Move[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]      With ActiveWorkbook[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]         .SaveAs ThisWorkbook.Path &"\" & Ary(i).Name, 51[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]         .Close[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]      End With[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]   Next I[/COLOR][/SIZE][/FONT]

End Sub

Thanks again for your help

 

[Fluff]

What is the value of i when it fails?
Also do you have Option Explicit at the vary top of the module, before any code?
If not, added it in & try to run the code again

 

[horizonflame]

Hi @Fluff Option Explicit is on at the top of the module. i = 0 on the error.

Thanks