VBA Code to extract Worksheets out of a Workbook

[suttonutd]

Hi, hoping someone can assist with using VBA to export worksheets. I have a workbook which has 30 reports (on separate worksheets) plus some other worksheets. One of the others worksheets is called "Export" which allows the user to select which reports to extracts using Range A1:A30. This range does match the worksheet name for each report.

I am trying to export the reports that have been selected in the export tab to their very own individual worksheet. There are a few others things I want to do but I'm sure I can work those out if I have a framework to work with.

I have this but it only copies the selected reports to just one workbook. Any ideas?? Any help would be massively appreciated.

Sub testArray()


Dim myArray() As String
Dim myRange As Range
Dim Cell As Range
Dim OldBook As String
Dim newBook As String
Dim a As Long


Set myRange = Application.Range(Cell1:="Export!A1:A30")
'Or the range with your sheetnames


OldBook = ActiveWorkbook.Name


For Each Cell In myRange
If Not Cell = "" Then
a = a + 1
ReDim Preserve myArray(1 To a)
myArray(a) = Cell
End If
Next


For a = 1 To UBound(myArray)
If a = 1 Then
Sheets(myArray(a)).Copy
newBook = ActiveWorkbook.Name
Workbooks(OldBook).Activate
Else
Sheets(myArray(a)).Copy After:=Workbooks(newBook).Sheets(a - 1)
Workbooks(OldBook).Activate
End If
Next
End Sub

 

[Fluff]

Do you mean each sheet should go into it's own Workbook?

 

[suttonutd]

Hi! Correct and there worksheet name matching report name. Ideally I need it saved too as the report name but I can probably sort something out for that but happy for any assistance on that matter too!

 

[Fluff]

How about

Sub suttonutd()
   Dim Cl As Range
   Dim Pth As String
   
   Pth = "C:\Mrexcel\Fluff\"
   Application.ScreenUpdating = False
   For Each Cl In Sheets("Export").Range("A1:A30")
      If Cl.Value <> "" Then
         Sheets(Cl.Value).Copy
         ActiveWorkbook.SaveAs Pth & Cl.Value & ".xlsx", 51
         ActiveWorkbook.Close False
      End If
   Next Cl
End Sub

 

[suttonutd]

that is more than ok. Thank you so much Fluff....

 

[Fluff]

You're welcome & thanks for the feedback