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

horizonflame

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

Some videos you may like

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)

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,756
Office Version
365
Platform
Windows
How about
Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,756
Office Version
365
Platform
Windows
Apologies the code should be
Code:
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
 

Fluff

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

horizonflame

Board Regular
Joined
Sep 27, 2018
Messages
166
Hi again @Fluff,

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

Code:
 .SaveAs ThisWorkbook.Path & "\" & Ary(i).Name, 51
Your help again would be appreciated :)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,756
Office Version
365
Platform
Windows
What is the value of Ary(i).Name when it fails?
Also what is the error message?
 

horizonflame

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

Code:
 Sub horizonflame2()
DimAry As Variant
Code:
[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
 
Last edited:

Fluff

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

Watch MrExcel Video

Forum statistics

Threads
1,095,726
Messages
5,446,152
Members
405,386
Latest member
xcookiemonster64

This Week's Hot Topics

Top