CODEWITHSUNDEEP
vbaexcel
user3436467
user3436467

Reputation: 1775

Automatically save certain sheets in workbook to CSV file

I have found a decent solution Saving excel worksheet to CSV files with filename+worksheet name using VB for saving all worksheets in a workbook as CSV files. However, I would like to be able to modify this code to only save worksheets where the sheet name ends with _t.

I am using the following code:

Sub SaveWorksheetsAsCsv()

Dim WS As Excel.Worksheet
Dim SaveToDirectory As String

Dim CurrentWorkbook As String
Dim CurrentFormat As Long

CurrentWorkbook = ThisWorkbook.FullName
CurrentFormat = ThisWorkbook.FileFormat
' Store current details for the workbook
SaveToDirectory = "H:\test\"
For Each WS In ThisWorkbook.Worksheets
    Sheets(WS.Name).Copy
    ActiveWorkbook.SaveAs Filename:=SaveToDirectory & ThisWorkbook.Name & "-" & WS.Name & ".csv", FileFormat:=xlCSV
    ActiveWorkbook.Close savechanges:=False
    ThisWorkbook.Activate
Next

Application.DisplayAlerts = False
ThisWorkbook.SaveAs Filename:=CurrentWorkbook, FileFormat:=CurrentFormat
Application.DisplayAlerts = True
' Temporarily turn alerts off to prevent the user being prompted
'  about overwriting the original file.

End Sub

Can this be done?

Upvotes: 0

Views: 269

Answers (1)

psychicebola
psychicebola

Reputation: 949

Use the Right or Instr-Method on the worksheet.name property to get the last characters / check if your searchstring is exisiting.

In your for each-loop add the following code:

If Right(WS.name, 2) = "_t" Then
  Sheets(WS.Name).Copy
  ActiveWorkbook.SaveAs Filename:=SaveToDirectory & ThisWorkbook.Name & "-" & WS.Name & ".csv", FileFormat:=xlCSV
  ActiveWorkbook.Close savechanges:=False
  ThisWorkbook.Activate        
End if

Upvotes: 1

Related Questions