CODEWITHSUNDEEP
excelvba
Irina
Irina

Reputation: 29

Saving a tab with a temporary name in Excel VBA as a separate csv file

I have a macro which creates a tab, named by a cell - every time I run the macro - this tab has different name. I have to save only this tab as a separate csv file.

For now I have the code below - it saves all 2 tabs to a specified location. I would be really grateful for any ideas how I can manage this ! 

Dim mySheet As Worksheet
Dim myPath As String
Application.DisplayAlerts = False
For Each mySheet In ActiveWorkbook.Worksheets
myPath = "\\F:\ABC\INPUT\"
Application.DisplayAlerts = False
ActiveWorkbook.Sheets(mySheet.Index).Copy
ActiveWorkbook.SaveAs Filename:=myPath & mySheet.Name, FileFormat:=xlCSV, CreateBackup:=True
ActiveWorkbook.Close
Application.DisplayAlerts = True
Next mySheet
Application.DisplayAlerts = False

Upvotes: 0

Views: 170

Answers (1)

Rishi Mehta
Rishi Mehta

Reputation: 417

In your question, you mentioned "I have a macro which creates a tab, named by a cell", so I am assuming that the tab is created based on the value in that cell. If that is the case, you may simply read the value of that cell in a vba variable. Something like:

Dim tabName as string
tabName = sheets("SheetName").range("A1").value 'if the cell for creating the sheet is A1

Now, use this variable to rename the file generated. Like,

ThisWorkbook.Worksheets(tabName).Copy 
ActiveWorkbook.SaveAs Filename:="F:\path\" & tabName & ".csv"

Upvotes: 2

Related Questions