Anyone see why this VBA isn't working as intended?

[sanantonio]

Sub save()

Dim fName As String
Dim path As String
Dim defaultPath As String

defaultPath = "T:\"
   path = Sheet1.Range("D18").Value
   If path = "" Then
        path = defaultPath
    End If
   If Right(path, 1) <> "\" Then
        path = path & "\"
    End If

fName = Range("A1")
Application.DisplayAlerts = False
On Error Resume Next
     Sheets("CSV").Visible = True
Sheets("CSV").Select
    Range("B34").Select
    Sheets("Order Upload DIS").Select
    Range("Table3[Copy all lines as of A3]").Select
    Selection.Copy
    Sheets("CSV").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

Dim theNewWorkbook As Workbook
Dim currentWorkbook As Workbook

Application.EnableEvents = False



'currentWorkbook is the source workbook, create a new workbook referencing to it with theNewWorkbook
Set currentWorkbook = ActiveWorkbook
Set theNewWorkbook = Workbooks.Add

'do the copy (it's better to check if there is already a 'Worksheet 1' in the new workbook. It it exists delete it or rename it
currentWorkbook.Worksheets("CSV").Copy before:=theNewWorkbook.Sheets(1)

'Remove default sheets in order to have only the copied sheet inside the new workbook
Dim i As Integer
For i = theNewWorkbook.Sheets.Count To 2 Step -1
    theNewWorkbook.Sheets(i).Delete
Next i
'Save File as XLSM
saveLocation = defaultPath & fName & ".csv"
theNewWorkbook.SaveAs Filename:=saveLocation, FileFormat:=xlCSV
theNewWorkbook.Close
     Sheets("CSV").Visible = False
     Sheets("Control").Select
     MsgBox saveLocation
End Sub

Basically it should only be saving to the defaultPath is D18 is blank. But regardless of whether D18 is blank or not it always saves to the default. Anyone see where I'm going wrong?

D18 contains: K:\New Folder\Testing\

 

[smozgur]

According to the code, you are supposed to use the path variable instead of defaultPath for the saveLocation variable assignment.

saveLocation = path & fName & ".csv"

 

[sanantonio]

I couldn't see the wood for the trees! Thank you!

 

[smozgur]

I couldn't see the wood for the trees! Thank you!

It happens to all
Glad to hear it is solved.

If you don't mind, I would suggest a couple of things for the existing code.

The following section:

Application.DisplayAlerts = False
On Error Resume Next
     Sheets("CSV").Visible = True
Sheets("CSV").Select
    Range("B34").Select
    Sheets("Order Upload DIS").Select
    Range("Table3[Copy all lines as of A3]").Select
    Selection.Copy
    Sheets("CSV").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

Could be replaced with the following. Instead of selecting, copying, and pasting, we can simply transfer the table column cell values as shown below.

   Dim rng As Range
    ThisWorkbook.Worksheets("CSV").Visible = True
    Set rng = ThisWorkbook.Worksheets("Order Upload DIS").Range("Table3[Copy all lines as of A3]")
    ThisWorkbook.Worksheets("CSV").Range("A1").Resize(rng.Rows.Count).Value = rng.Value

I like how you delete the unnecessary worksheets from the new workbook starting from the last worksheet. I would only disable and enable alerts to avoid the deletion confirmation message. The current code already does the disabling part but it is better to disable the alerts right before the worksheet deletion and enabling back right after the deletion is completed.

        'Remove default sheets in order to have only the copied sheet inside the new workbook
        Application.DisplayAlerts = False
        For i = theNewWorkbook.Worksheets.Count To 2 Step -1
            theNewWorkbook.Worksheets(i).Delete
        Next i
        Application.DisplayAlerts = True

And, I wouldn't use "On Error Resume Next" since the current structure doesn't look like that it needs error handlers.

 

[sanantonio]

Super helpful thank you, I'll take a look at what I can trim next time I've some optimization time on the calendar!