Macro/VBA code to Copy data from primary worksheet to a worksheet opened by macro

Hello,

I have a new problem. I created a code that will open a file based on a cell. My dilema is that once the file is opened, I want it to go back to the original file (Esc Disb Automation.xls) and copy the data in the selected tab into a new tab in the opened file (the one that was opened by the macro) and paste it there and rename that file based on a date in a specific cell (G1) in that sheet. Basically, once the script below opens the file specified in cell I1, I want it to:
1) Go back to "Esc Disb Automation.XLS"
2) Go to sheet labeled "944300"
3) Copy that whoel sheet
4) Go back to the spreadsheet that was opened from cell I1
5) Paste the sheet into the opened workbook from cell I1
6) Rename the tab based on cell G1
7) Save the file

The path name in cell I1 is based on the date in cell G1. If the date was 3/30/2013, then the file it will open will be the March-2013 file. For instancem, the current file name in cell I1 is K:\shared\InvestorBankRec\Reconciliations\944300 ESCROW DISB\2013 Reconciliation\944300 - Bank Statement - 2013\944300 - 03-13 Statements.xls
However, if the date is 4/22/2013 the file name would be K:\shared\InvestorBankRec\Reconciliations\944300 ESCROW DISB\2013 Reconciliation\944300 - Bank Statement - 2013\944300 - 04-13 Statements.xls

I don't want the code to read the exact file name because the name of the file will change every month but daily activity will be saved to the monthly file. Is there a way to get the data to copy and paste into the file that is opened from my macro below?

If I need to explain more, please let me know. If there is a much better way to do this, please let me know as well. I need this done for 4 other sheets in the Esc Disb Automation.xls and was thinking to just have it run for one tab (the 944300) and copy it for the other 4 tabs (937698, 937839, 976601, and 644597445). Can this be looped?

Please Login or Register  to view this content.

Thank you,
Miriam

Never mind, I just solved my own problem. On to my fnal problem ...

Not a problem. I got it to work by using the following code:

Please Login or Register  to view this content.

If anyone knows of a shorter way to accomplish this, please let me know. Otherwise, this did solve my problem.


Thank you!
~Miriam