Apologies for the vague descriptive title, but I was unsure how else to word it.

Basically I am doing some automation using several workbooks at work and although certain parts of my code work, others do not work at all.

The basic premise is, the user enters a date via an input box as MMM YYYY, which is used to open up sheets from a specified location which contain the date entered. So far so good.

However, I then want to feed information from the opened up sheet into the main workbook. Whereas this works perfectly for a Vlookup, the workbook doesn't recognise the date entered for another tab where information is to be copied across from one spreadsheet onto the main workbook.

An edited version of my code is pasted below:



Dim DT As Date


'INPUT BOX TAKES DATE FROM USER
DT = Application.InputBox("Enter a Date as MMM YYYY")
DT = Format(DT, "MMM YYYY")

Dim strName As String
Dim strPath As String


strPath = "R:\CreditRisk\Analytics\Team\Individual Folders\Shruti Sharma\Bahvioural Monitoring\"

strName = strFilePath & "CHAR_LT_1_" & Left(Format(DT, "MMM YYYY"), 3) & "_" & Right(Format(DT, "MMM YYYY"), 4) & ".xls"


If Dir(strName) = "" Then
MsgBox "File " & strName & " does not exist...", vbExclamation, "File not found"
Else
Workbooks.Open strName
End If

MsgBox "The name of the active sheet is " & ActiveSheet.Name
CURRBOOK.Sheets("Characteristic Stability 2").Activate
' Sheets("Characteristic Stability 2").Select
Range("C9").Select
ActiveCell.FormulaR1C1 = "=strName!R2C2"


Basically, where I specify the full name of the external workbook, the details are picked up easily, but where the "strName" is entered, it isn't recognised.

The solution is probably very simple, but as a new user of VBA I would be very grateful for any help or advice.

Thanks