How to concatenate a variable with a Range

Following advice from this forum I have developed the following code to save a workbook with a filename made up from Ranges within a worksheet. What I had also hoped to do was include the first 2 letters of a Range.

The code that works is as follows

Private Sub SaveFile_Click()
'
' SaveFile Macro
'
Dim Dt As Date
Dim Ex As String

Dt = Range("StartDate").Value
Ex = Left(ExIncOp, 2)
'
        If IsEmpty(Worksheets("Operation").Range("ExIncOp")) Then
    Msg = MsgBox("Please enter TYPE in cell C6 to continue ", vbOKOnly)
        End If
        
        If IsEmpty(Worksheets("Operation").Range("Name")) Then
    Msg = MsgBox("Please enter NAME in cell H6 to continue ", vbOKOnly)
        End If
    
        If IsEmpty(Worksheets("Operation").Range("StartDate")) Then
    Msg = MsgBox("Please enter DATE FROM in cell E10 to continue", vbOKOnly)
        
Exit Sub
        End If

    ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" &  _
Range(Debrief_Folder) & "\" & Range("Name").Value & " " & Format _
(Dt, "dd-mmm-yy") & ".xls", FileFormat:=xlNormal

End Sub

This code includes the line

Ex = Left(ExIncOp, 2)

which I believe creates a variable Ex made up of the first 2 characters of the Range ExIncOp.
How do I then introduce this variable into the ActiveWorkbook line?
I have tried

Range("Ex").Value

but I realise Ex is not a Range so this fails.

Hi Leith / cremate,

I had already found the error in lack of " " and the code as follows works

Private Sub SaveFile_Click()
'
' SaveFile Macro
'
Dim Dt As Date
Dim Ex As String

Dt = Range("StartDate").Value
Ex = Left("ExIncOp", 2)

        If IsEmpty(Worksheets("Operation").Range("ExIncOp")) Then
    Msg = MsgBox("Please enter TYPE in cell C6 to continue ", vbOKOnly)
        End If
        
        If IsEmpty(Worksheets("Operation").Range("Name")) Then
    Msg = MsgBox("Please enter NAME in cell H6 to continue ", vbOKOnly)
        End If
    
        If IsEmpty(Worksheets("Operation").Range("StartDate")) Then
    Msg = MsgBox("Please enter DATE FROM in cell E10 to continue", vbOKOnly)
        
Exit Sub
        End If

     ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & Range _
     ("Debrief_Folder").Value & "\" & Range("Name"). _
     Value & " " & Format(Dt, "dd-mm-yy") & ".xls", FileFormat:=xlNormal
'     & Range("Ex").Value
    
End Sub

But if I include the highlighted red code as follows

     ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & Range _
     ("Debrief_Folder").Value & "\" & Range("Ex").Value & Range("Name"). _
     Value & " " & Format(Dt, "dd-mm-yy") & ".xls", FileFormat:=xlNormal

it fails even if I take the " " from around Ex

Regards

I have a template worksheet where the user enters data in three cells which are also named Ranges.

I have a button on the sheet which should save the file with the first two letters of the first Range, a space, the second Range, a space, and the third Range.

The file name should look something like In Test 29-Feb-08.xls.

The code I have works without the first two letters of the first Range.

Private Sub SaveFile_Click()
'
' SaveFile Macro
'
Dim Dt As Date
Dim Ex As String

Dt = Range("StartDate").Value
Ex = Left("ExIncOp", 2)

        If IsEmpty(Worksheets("Operation").Range("ExIncOp")) Then
    Msg = MsgBox("Please enter TYPE in cell C6 to continue ", vbOKOnly)
        End If
        
        If IsEmpty(Worksheets("Operation").Range("Name")) Then
    Msg = MsgBox("Please enter NAME in cell H6 to continue ", vbOKOnly)
        End If
    
        If IsEmpty(Worksheets("Operation").Range("StartDate")) Then
    Msg = MsgBox("Please enter DATE FROM in cell E10 to continue", vbOKOnly)
        
Exit Sub
        End If
  
     ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & Range _
     ("Debrief_Folder").Value & "\" & Range("Name"). _
     Value & " " & Format(Dt, "dd-mm-yy") & ".xls", FileFormat:=xlNormal

    

End Sub

I would like to add those two letters to the file name by replacing the red code with something like

ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & Range _
     ("Debrief_Folder").Value & "\" & Range("Ex").Value & " " & Range("Name"). _
     Value & " " & Format(Dt, "dd-mm-yy") & ".xls", FileFormat:=xlNormal

The magenta code is my addition, which does not work
Can anyone see the error?

Thanks in advance

Leith,

Thanks for your response, which made complete sense to me and you understood exactly what I required, however I still get the following error

Run-time eror '1004'
Method 'Range' of object '_Worksheet' failed

I attach the .xlt file and hope you can see what is going wrong. If you cannot, I will be happy without the first 2 letters of Range 'ExIncOp'!

Regards

Hello Leith,

I was trying to keep the file size small and forgot about the other links!

Here is the Workbook with all the sheets.

Thanks for your help, again