hi,

i have spent far too long on this and its making me go crazy. I've made many attempts using different methods but the below code probably shows exactly what i want to do. I've tried "setting" the workbook name but if its already open it asks if i want to reopen it and wipe unsaved data.

anyway, i have two workbooks(4 really, but repeating steps should be fine). I need to copy the first sheet from a workbook named on today's date(which is always open, "thestring" in my code), and then paste it into the active workbook. if im not using variables it works, or i use these variables just to open the workbook, it works.

i get run time error '9', subscript is out of range. am i using the wrong code? or am i naming the workbook incorrectly? 

Sub finalcopy()


Dim TheString As String, TheDate As Date, ThePath As String, Theopenwkb As String
'both workbooks are always open

ThePath = "C:\Users\Jeremy\testing"
TheDate = Date
TheString = "\firstone" & WorksheetFunction.Text(TheDate, "YYYYMMDD") & ".xlsx"


'this is the main workbook, and where the macro will start/be saved in.
Theopenwkb = "\testingclear.xlsm"



Workbooks(ThePath & TheString).Sheets("Sheet1").Copy Before:=Workbooks(Theopenwkb).Sheets(1)




End Sub