Export all sheets to CSV.. EXCEPT ONE - code modification

albytross

New Member
Joined
Sep 22, 2021
Messages
24
Office Version
  1. 365
Hi,

I have this code to export all my sheets to CSV.

How can I export all worksheets except for a worksheet named "DONT_EXPORT"?
VBA Code:
[QUOTE]
Sub Save_Worksheets_to_CSV()
        Dim xWs As Worksheet
        Dim xcsvFile As String
        Dim Pth As String
        Application.ScreenUpdating = False
        
      With Application.FileDialog(4)
         .AllowMultiSelect = False
         .InitialFileName = "C:\Data\Exports\"
         If .Show Then Pth = .SelectedItems(1)
      End With

        For Each xWs In Application.ActiveWorkbook.Worksheets
            xWs.Copy
            xcsvFile = Pth & "\" & xWs.Name & ".csv"
            Application.ActiveWorkbook.SaveAs fileName:=xcsvFile, _
            FileFormat:=xlCSV, CreateBackup:=False
            Application.ActiveWorkbook.Saved = True
            Application.ActiveWorkbook.Close
        Next
    End Sub
[/QUOTE]
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Add an If /End If:
VBA Code:
        For Each xWs In Application.ActiveWorkbook.Worksheets
            If Left(xWs.Name, 11) <> "DONT_EXPORT" Then
                xWs.Copy
                xcsvFile = Pth & "\" & xWs.Name & ".csv"
                Application.ActiveWorkbook.SaveAs Filename:=xcsvFile, _
                FileFormat:=xlCSV, CreateBackup:=False
                Application.ActiveWorkbook.Saved = True
                Application.ActiveWorkbook.Close
            End If
        Next
Sheets whose name "starts" with DONT_EXPORT will be ignored
 
Upvote 0
Hi there...

You can modify the code to skip the worksheet named "DONT_EXPORT" by adding an If statement to check for the worksheet name. Here is the modified code:

VBA Code:
Sub Save_Worksheets_to_CSV()
    Dim xWs As Worksheet
    Dim xcsvFile As String
    Dim Pth As String
    Application.ScreenUpdating = False
    
    With Application.FileDialog(4)
        .AllowMultiSelect = False
        .InitialFileName = "C:\Data\Exports\"
        If .Show Then Pth = .SelectedItems(1)
    End With

    For Each xWs In Application.ActiveWorkbook.Worksheets
        If xWs.Name <> "DONT_EXPORT" Then ' Check if worksheet name is not "DONT_EXPORT"
            xWs.Copy
            xcsvFile = Pth & "\" & xWs.Name & ".csv"
            Application.ActiveWorkbook.SaveAs fileName:=xcsvFile, _
                FileFormat:=xlCSV, CreateBackup:=False
            Application.ActiveWorkbook.Saved = True
            Application.ActiveWorkbook.Close
        End If
    Next
End Sub



This modified code checks if the current worksheet's name is not "DONT_EXPORT" before proceeding with the export. If the name is "DONT_EXPORT", the code skips the worksheet and moves on to the next one.
 
Upvote 0
hi guys both work great. I've tried to applied the line to other code such as the renaming VBA below, but doesnt work - resulting in "next without for"

VBA Code:
Sub RenameAllWorksheets()

Sheets("123").Name = "XYZ"
Sheets("456).Name = "ABC"
Sheets("SOURCE_DATA").Name = "DONT_EXPORT"

 
 Dim sh As Worksheet
    For Each sh In ActiveWorkbook.Worksheets
        ! Insert code to skip renaming DONT EXPORT here.. but how!'
        sh.Name = Range("C9") & sh.Name
    Next sh
End Sub
 
Upvote 0
Maybe...

VBA Code:
Sub Export_Rename_Worksheets()
    Dim xWs As Worksheet
    Dim xcsvFile As String
    Dim Pth As String
    Application.ScreenUpdating = False
    
    With Application.FileDialog(4)
        .AllowMultiSelect = False
        .InitialFileName = "C:\Data\Exports\"
        If .Show Then Pth = .SelectedItems(1)
    End With

    Sheets("123").Name = "XYZ"
    Sheets("456").Name = "ABC"
    Sheets("SOURCE_DATA").Name = "DONT_EXPORT"

    For Each xWs In Application.ActiveWorkbook.Worksheets
        If xWs.Name <> "DONT_EXPORT" Then ' Check if worksheet name is not "DONT_EXPORT"
            xWs.Name = Range("C9") & xWs.Name
            xWs.Copy
            xcsvFile = Pth & "\" & xWs.Name & ".csv"
            Application.ActiveWorkbook.SaveAs fileName:=xcsvFile, _
                FileFormat:=xlCSV, CreateBackup:=False
            Application.ActiveWorkbook.Saved = True
            Application.ActiveWorkbook.Close
        End If
    Next
End Sub
 
Upvote 0
Maybe...

VBA Code:
Sub Export_Rename_Worksheets()
    Dim xWs As Worksheet
    Dim xcsvFile As String
    Dim Pth As String
    Application.ScreenUpdating = False
   
    With Application.FileDialog(4)
        .AllowMultiSelect = False
        .InitialFileName = "C:\Data\Exports\"
        If .Show Then Pth = .SelectedItems(1)
    End With

    Sheets("123").Name = "XYZ"
    Sheets("456").Name = "ABC"
    Sheets("SOURCE_DATA").Name = "DONT_EXPORT"

    For Each xWs In Application.ActiveWorkbook.Worksheets
        If xWs.Name <> "DONT_EXPORT" Then ' Check if worksheet name is not "DONT_EXPORT"
            xWs.Name = Range("C9") & xWs.Name
            xWs.Copy
            xcsvFile = Pth & "\" & xWs.Name & ".csv"
            Application.ActiveWorkbook.SaveAs fileName:=xcsvFile, _
                FileFormat:=xlCSV, CreateBackup:=False
            Application.ActiveWorkbook.Saved = True
            Application.ActiveWorkbook.Close
        End If
    Next
End Sub
Hi Jimmypop,
Thanks for trying to combine the code, but i didn't intend for that. Really appreciate it though, i should have been clearer sorry.

Anyway to make the code in post #4 skip renaming said file - without worrying about the CSV export side of things
 
Upvote 0
Hi

I just thought you wanted to combine this because of the line

VBA Code:
! Insert code to skip renaming DONT EXPORT here.. but how!'

So you just want to rename the sheets in a seperate module?
 
Upvote 0
Sorry Jim

Is there standard syntax to discount a sheet by the name X from any macro?
 
Upvote 0
Hi there...

So you want to exclude certain named worksheets from your macro...
 
Upvote 0

Forum statistics

Threads
1,223,904
Messages
6,175,295
Members
452,631
Latest member
a_potato

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top