Copy specific sheets from a workbook to another workbook, excluding macro

Question

I have an application in Excel with macro. It imports any number of sheets to the workbook and performs certain calculation in each file except to the one file (user interface).

I want to make a hard copy of the sheets except the user interface sheet. Basically it has to create a new workbook and copy the contents of my application to the new workbook and save it without macro. I tried different approaches, however most save the application as is.

Sub Save_files()
    Dim Current As Worksheet
    Dim newBook As Workbook
    Dim newPath As String
        
    newPath = ThisWorkbook.Path & "\" & "RM_" & myDate & ".xlsx"
    Set newBook = Workbooks.Add
    ' Loop through all of the worksheets in the active workbook.
    For Each Current In Worksheets
        'If Current.Name <> "Start" Or Current.Name <> "Exception" Then
            Current.Copy Before:=newBook.Sheets(1)
            newBook.SaveAs fileName:=newPath
        'End If
    Next
End Sub

I was using this code that can save a single sheet and then I added a for loop to iterate over all sheets and save them but it failed many times.

If you do not share the code copying the sheets by iteration, which should be something simple and safe, it is difficult to imagine what kind of problem you faced... When you say " iterate over all sheets and save them" do you mean saving of each file individually, or copying all of them in another workbook and save this one at the end? — FaneDuru
thanks for the response. What a meant is i have to create a new workbook and save all the files into the same workbook. that means when i open the new workbook, it should have all the files that i haved saved. It is basically coping all of them into a new workbook. — abanfo
Please update your code to include the For...Loop that you mentioned fails many times. We can't help if we can't see what's going wrong! — Samuel Everson
You did not define the workbook where the iteration takes place. After adding the new workbook, this one is the active workbook. — FaneDuru

FaneDuru FaneDuru · Accepted Answer · 2020-12-24T10:58:13

Try the next way, please:

Sub Save_files()
    Dim Current As Worksheet, CopyWB As Workbook
    Dim newBook As Workbook, MyDate As String
    Dim newPath As String
    
    MyDate = Format(Date, "dd_mm_YY") 'the format you need
    Set CopyWB = ActiveWorkbook 'please, use here the workbook to copy from
    newPath = ThisWorkbook.Path & "\" & "RM_" & MyDate & ".xlsx"
    Set newBook = Workbooks.Add
         ' Loop through all of the worksheets in the active workbook.
        For Each Current In CopyWB.Worksheets
'            If Current.Name <> "Start" Or Current.Name <> "Exception" Then
                Current.Copy Before:=newBook.Sheets(1)
'            End If
         Next
         newBook.SaveAs fileName:=newPath
End Sub

Copy specific sheets from a workbook to another workbook, excluding macro

1 Answers