Switch between workbooks in a macro in different sub routine

[russ8502]

I am working on a budget model where the model workbook is a read only file and it allows the user to select the data workbook they want to work on. This sub routine copies all of the data from the data workbook into the model workbook. I am using the Application.GetOpenFilename command to open the data workbook. That works fine. My problem is after the user has entered all of there budget data, they will click on a button that starts a new macro that will copy the data they just entered back to the data workbook and save it so the next time they go back into the model their data is where they left off. I can't get the sub routinue to do this part without having to use the Application.GetOpenFilename again in the code. This brings up the file open box again and they have to select the same data workbook they selected when they began or risk writing over another file.

How can I get this new sub to recoginize the two different workbooks without having to use the GetOpenFilename again. I want it to switch between the open workbooks?

I am open to any other commands that will do this. I am not locked in to GetOpenFilename.

Thanks

 

[Norie]

Well GetOpenFilename is probably your best bet to get the workbook name.

But I'm afraid the process is rather unclear.

For example does this mean data is entered manually by the user?

the user has entered all of there budget data

Also why does the data workbook need to be reopened?

 

[russ8502]

Yes the user enters the budget data in the budget model file and then the entered data is copied back out to the data file and saved so that if they have more then one cost center to do then they can open the next cost center data file and work on it.

The data file when distributed to the user only has a 2007 projected budget by month going across columns. When they open the model it will let them choose the data file they want to work on and then it will open that data workbook and copy the 2007 projected budget into the model workbook so they can use it as a guide to do their 2008 budget. They will then enter their budget for 2008 in the columns past the 2007 Projected budget columns. When they finish I want them to click on a button that says the are finished. This button click will start the sub routine to copy everything they just entered back out to the data workbook and save the workbook. If they need to go back to that budget it will then pull all of the data they entered earlier back into the model workbook so they can update it. My problem is getting the macro to recoginze that the data workbook is still open so it can copy from the model workbook to the data workbook without having to use the GetOpenFilename command again in the copy sub routine. If I have to use the GetOpenFilename in the copy sub it makes them have to choose from the list of files again. If they choose the wrong one it will write over the wrong file.

Hope is clearer even though it's wordy.

Thanks