VBA Saving tabs as CVS files based on sheet names and date

Kpersen

New Member
Joined
Jan 29, 2018
Messages
25
Good morning.

I hope some one can help me a piece of VBA that I just can not wrap my head around:

I have a workbook with a number of different sheets where a macro can save each of these sheets to a CVS naming the files using the sheet names + a specific word and the current date and time. There is an exception in the VBA that requires this to look for a sheet named USCS and sae this with a slightly different specific text from the other sheets.

Here is my macro:

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
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
End Sub

The existing macro above saves the USCS sheet as US_DGF_USCS_CARe2_YYYYMMDDHHMM

Now, I have added a new sheet named USCS3 which I need to save with all the same rules except this sheet must be saved as US_DGF_USCS_CARe3_YYYYMMDDHHMM. Meaning the number 3 in the sheet name must be disregarded.
The existing saves the USCS sheet as US_DGF_USCS_CARe2_YYYYMMDDHHMM
There should be no changes to the other sheets.

Can anyone tell me how I add this exception to the above macro?

Thank you so much
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,678
Office Version
365
Platform
Windows
How about
Code:
If xWs.Name = "USCS" Then
   xWs.SaveAs xDir & "" & "US_DGF_" & xWs.Name & "_CARe2_" & Dt, xlCSV, Local:=True
ElseIf xWs.Name = "USCS3" Then
   xWs.SaveAs xDir & "" & "US_DGF_" & Left(xWs.Name, 4) & "_CARe3_" & Dt, xlCSV, Local:=True
Else
   xWs.SaveAs xDir & "" & "US_DGF_" & xWs.Name & "_CARe_" & Dt, xlCSV, Local:=True
End If
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,678
Office Version
365
Platform
Windows
Glad to help & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,095,623
Messages
5,445,557
Members
405,339
Latest member
dayanand008

This Week's Hot Topics

Top