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

      With ActiveWorkbook
         .SaveAs .path & "\" & Ary(i).name, 51
         .Close
      End With

 

[horizonflame]

Still i = 0 error I'm afraid

 

[Fluff]

In that case what is the actual sheet name of sheet6?
Also does it move that sheet to a new workbook?

 

[horizonflame]

Yes, Sheet6 moves to a new sheet before the error kicks in.

Sheet6 is called "CWDP_HH_1205_Allocate_01"

That name changes every week based upon date change

 

[Fluff]

Ok, this should work

Sub horizonflame()
   Dim Ary As Variant
   Dim i As Long
   Dim Fname As String
   
   Ary = Array(Sheet6, Sheet7, Sheet8, Sheet9, Sheet10, Sheet11, Sheet12, Sheet13, Sheet14, Sheet15, Sheet16, Sheet17, Sheet18, Sheet19, Sheet20, Sheet21, Sheet22)
   For i = 0 To UBound(Ary)
      Fname = Ary(i).Name
      Ary(i).Move
      With ActiveWorkbook
         .SaveAs ThisWorkbook.path & "\" & Fname, 51
         .Close
      End With
   Next i
End Sub

 

[horizonflame]

Yes! Thank you so much for your help :D

 

[Fluff]

You're welcome & thanks for the feedback