CODEWITHSUNDEEP
excelvba
abanfo
abanfo

Reputation: 11

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

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.

Upvotes: 1

Views: 153

Answers (1)

FaneDuru
FaneDuru

Reputation: 42236

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

Upvotes: 0

Related Questions