Save tabs as individual files with exceptions

Kpersen

New Member
Joined
Jan 29, 2018
Messages
25
I am hoping someone can help me solve a puzzle here. I have a workbook with a number of tabs for which I have created macro to save each of these as it's own VBA file. When the files are saved the macro is designed to use the name of the tab combined with a predefined value and a date and time.


Here is my issue. I have only single tab that I wish to exclude, as it needs a different naming convention but I cannot figure out how to exclude it from the macro I have and then get the same macro to save that file with a different naming convention.


Here is what I have:


Public Sub SaveWorksheetsAsCsv222()
Dim xWs As Worksheet
Dim xDir As String
Dim folder As FileDialog
dt = Format(CStr(Now), "YYYYMMDDHHMM")
Set folder = Application.FileDialog(msoFileDialogFolderPicker)
If folder.Show <> -1 Then Exit Sub
xDir = folder.SelectedItems(1)
For Each xWs In Application.ActiveWorkbook.Worksheets
xWs.SaveAs xDir & "" & "US_DGF_" & xWs.Name & "_CARe_" & dt, xlCSV, Local:=True
Next
End Sub


What I need is for a tab named USCS to be saved using the same criteria but instead of "CARe: it needs to be CARe2 - Just this tab only.


Can anyone help please?
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,557
Try. Adjust "SpecialWorksheet" as needed.
Code:
For Each xWs In Application.ActiveWorkbook.Worksheets
    If xWs.Name <> "SpecialWorksheet" Then
        xWs.SaveAs xDir & "" & "US_DGF_" & xWs.Name & "_CARe_" & dt, xlCSV, Local:=True
    End If
Next
 

Kpersen

New Member
Joined
Jan 29, 2018
Messages
25
Try. Adjust "SpecialWorksheet" as needed.
Code:
For Each xWs In Application.ActiveWorkbook.Worksheets
    If xWs.Name <> "SpecialWorksheet" Then
        xWs.SaveAs xDir & "" & "US_DGF_" & xWs.Name & "_CARe_" & dt, xlCSV, Local:=True
    End If
Next
Thank you for you help. I was unable to make this work. The macro instead saved the other worksheets with an incorrect name.
I need the macro to save all the worksheets as single CSV file the only difference is that the worksheet names "USCS" must be have the number 2 after "CARe" in the name like "CARe2" - when I tried to modify this, it doubled the date and time in the file names for the other worksheets.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,719
Office Version
365
Platform
Windows
How about
Code:
For Each xWs In Application.ActiveWorkbook.Worksheets
   If xWs.Name <> "USCS" Then
      xWs.SaveAs xDir & "" & "US_DGF_" & xWs.Name & "_CARe_" & dt, xlCSV, Local:=True
   Else
      xWs.SaveAs xDir & "" & "US_DGF_" & xWs.Name & "_CARe2_" & dt, xlCSV, Local:=True
   End If
Next xWs
 

Kpersen

New Member
Joined
Jan 29, 2018
Messages
25
How about
Code:
For Each xWs In Application.ActiveWorkbook.Worksheets
   If xWs.Name <> "USCS" Then
      xWs.SaveAs xDir & "" & "US_DGF_" & xWs.Name & "_CARe_" & dt, xlCSV, Local:=True
   Else
      xWs.SaveAs xDir & "" & "US_DGF_" & xWs.Name & "_CARe2_" & dt, xlCSV, Local:=True
   End If
Next xWs
Thank you so much. I got it to work
 

Watch MrExcel Video

Forum statistics

Threads
1,099,137
Messages
5,466,898
Members
406,507
Latest member
donwiss

This Week's Hot Topics

Top