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

[albytross]

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"?

[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]

 

[Anthony47]

Add an If /End If:

        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

 

[Jimmypop]

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:

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.

 

[albytross]

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"

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

 

[Jimmypop]

Maybe...

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

 

[albytross]

Maybe...

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

 

[Jimmypop]

Hi

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

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

So you just want to rename the sheets in a seperate module?

 

[albytross]

Sorry Jim

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

 

[Jimmypop]

Hi there...

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