Copying worksheet from another open workbook using variables, paste to active workbook

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?

Please Login or Register  to view this content.

that works, and thanks a lot for your help but i was trying to include the full name to ensure if the copy workbook was saved in a different folder to the "testingclear.xlsm" it would still copy(sorry, in hindsight i didn't make that very clear). am i doing it wrong if i get the error when i move the file to a different sheet? or does this method only work if they are both in the same folder?

thanks a lot for your help, i foolishly assumed you had to give the full file name when using workbook names, even when they are open. i assume this is part of the reason that you can only have one workbook open when they have the same names, even if they are saved in different locations.
thanks again