CODEWITHSUNDEEP
vbaexcelcsv
Andrew P.
Andrew P.

Reputation: 19

VBA: Select specific WorkSheet as Active after CSV save and close

I have a macro that fills in then saves tracking numbers to a .csv file and I want to have Worksheets(1) active when I close the Workbook. The problem I am having is that it will save and close the Workbook on Worksheets("Tracking"). I have tried to select or activate Worksheets(1) then re-save and close but I can't seem to have it work.

Here is my code for saving the .csv file and closing the workbook.

Dim ws as Worksheet
Set ws = ActiveWorkbook.Worksheets("Tracking")    
ws.Select

Dim sFileName As String
sFileName = ActiveWorkbook.path & "\" & Left(ws.name, InStr(1, ActiveWorkbook.name, ".") - 1) & ".csv"

Application.DisplayAlerts = False

'Save Current workbook just in case latest additions not saved.
ActiveWorkbook.Save

'Now create a CSV of the active sheet.
ws.SaveAs Filename:=sFileName, FileFormat:=xlCSV, CreateBackup:=False

Application.DisplayAlerts = True 'turn it back on
ActiveWorkbook.Close True 'Close and quit excel
Application.Quit

Upvotes: 0

Views: 1144

Answers (1)

Mircea
Mircea

Reputation: 188

If all you are trying to do is have excel open on a specific sheet, you can try using the built-in workbook_open.

Private Sub Workbook_Open()
    Sheets(“Sheet1”).Select
End Sub

The changes for selecting the sheet and the range.

Sub Macro1()
Dim ws As Worksheet
Set ws = ActiveWorkbook.Worksheets("Sheet2")
ws.Select

Dim sFileName As String
sFileName = ActiveWorkbook.Path & "\" & Left(ws.Name, InStr(1, ActiveWorkbook.Name, ".") - 1) & ".csv"

Application.DisplayAlerts = False

'Save Current workbook just in case latest additions not saved.
Sheets(1).Select
Range("A1").Select
ActiveWorkbook.Save

'Now create a CSV of the active sheet.
ws.SaveAs Filename:=sFileName, FileFormat:=xlCSV, CreateBackup:=False

Application.DisplayAlerts = True 'turn it back on
ActiveWorkbook.Close True 'Close and quit excel
Application.Quit
End Sub

Upvotes: 1

Related Questions