Excel VBA Save path/name from cells with variables

0
votes

I am using cells as the file path and filename to save a copy of my workbook.

Here's the code am using now but it puts spaces in between each cell. Note only the ActiveSheet.Range cells will have the possibility of being blank

Dim NewWb As Workbook
sFile = Control_Sheet_VB.Range("H2") & "\" & ActiveSheet.Range("H8") & " " & ActiveSheet.Range("E10") & " " & ActiveSheet.Range("D14") & " - Ticket #" & Control_Sheet_VB.Range("B2") & Control_Sheet_VB.Range("C2") & ".xlsm"
Set OldWb = ActiveWorkbook
OldWb.SaveCopyAs sFile
Set NewWb = Workbooks.Open(sFile)

How can I make it so if some of the cells that are used as the file name are blank then it dose not put the extra space in the filename

1
vbaexcel
Does surrounding each reference with e.g. Trim(ActiveSheet.Range("H8")) solve your issue? - Victor Moraes

1 Answers

2
votes

you may go like this:

sFile = Control_Sheet_VB.Range("H2") & "\" & _
        IIf(ActiveSheet.Range("H8") <> "", ActiveSheet.Range("H8") & " ", "") & _
        IIf(ActiveSheet.Range("E10") <> "", ActiveSheet.Range("E10") & " ", "") & _
        IIf(ActiveSheet.Range("D14") <> "", ActiveSheet.Range("D14") & " ", "") & _
        " - Ticket #" & Control_Sheet_VB.Range("B2") & _
        Control_Sheet_VB.Range("C2") & ".xlsm"