SaveAs to a File with name from Clipboard

Hello all,

I am stuck in a macro that I am making that automatically formats an excel document. I will have several questions throughout the process I am sure, but I will start with the SaveAs process.

I would also like to note that I am not new to programming, but I am very new to VBA and there is likely simple answers to my questions, so thank you for your patience.

So, I am trying to save my file as a specific name that is a portion of a value from a specific cell in the second sheet of the workbook. Here is the code.

'

Sheets("Sheet2").Select
Range("F6").Select
Dim objData As New DataObject
Dim strTemp As String
strTemp = ActiveCell.Value
objData.SetText (strTemp)
objData.PutInClipboard

Dim DataObj As New MSForms.DataObject
Dim fileName As String
DataObj.GetFromClipboard
fileName = DataObj.GetText

ActiveWorkbook.SaveAs fileName:="C:\Users\a87078\Desktop\Splunk Documents\Splunk" & fileName & ".xlsm", FileFormat:=52, CreateBackup:=False

'

Clipboard functions confuse me, as solutions never seem to be consistent on forums. I am getting an error on the "fileName" portion of the last line. I need to remove a portion of the data from the cell aswell, such as "This is the CellValue" and I need it to SaveAs only "CellValue", which I am accomplishing using a SUBSTITUTE() function given that the "This is the " portion is always a constant and doesn't change from workbook to workbook.

All of this together is proving to be a lot of trouble. Any help would be greatly appreciated. The next step in the macro will be regarding selecting specific ranges of cells based on their contents (as well as an offset cell one to the right of said cell.)

Thanks!