VBA: create new workbook, rename, copy sheets from original to new with exceptions

[kweaver]

From a VBA routine, I want to create a new workbook, rename it (so that I can date stamp the name), then copy sheets from original workbook except certain sheets (only 3 not copied).
Then delete the copied sheets from the original workbook.

I've been messing with this for over an hour and can't get it right. Thanks in advance.

 

[Takae]

Please try it with a workbook which has at least four sheets.

Sub test()
Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Move
ActiveWorkbook.SaveAs Filename:="C:\Users\" & Environ("username") & "\Desktop\" & "ABC" & Format(Date, "mmyyyy") & ".xlsx"
ActiveWorkbook.Close
End Sub

 

[kweaver]

Thanks Takae for your suggest. My issue is that I don't know which sheets by name will be moved because it changes every time the user runs the routine.

 

[Fluff]

How would a macro determine which sheets should be copied & which should be deleted?

 

[kweaver]

The three not to be deleted are always the same three.
The ones to move and delete vary but are named in a sheet called "TextFile" in the range S5 to Sn

 

[kweaver]

BTW: if it matters, I need to move the sheets and also retain their formatting (especially the column width).

 

[Fluff]

Ok, how about

Sub kweaver()
   Dim UsdRws As Long, i As Long
   Dim Cl As Range
   Dim Ary As Variant
   
   With Sheets("TextFile")
      UsdRws = .Range("S" & Rows.Count).End(xlUp).Row
      ReDim Ary(UsdRws - 5)
      For Each Cl In .Range("S5:S" & UsdRws)
         Ary(i) = Cl.Value
         i = i + 1
      Next Cl
   End With
   Sheets(Ary).Move
End Sub

 

[kweaver]

Fluff: thanks. I haven't tried it yet, but shouldn't this macro create the new workbook and then move those in the array (Ary) to it?

Just tried it and the new sheet has the typical "Book" name to it. How do I then rename it and save that workbook?

 

[Fluff]

It will create the new workbook automatically.

 

[kweaver]

RIGHT! That did it! As usual, you have come to my rescue! Much appreciated.
AND, I have learned something else about using arrays. Double benefit of your expertise.