can you make my code smarter?

[Eurekaonide]

Hi All

I ahve some code that is actually working fine, however I wonder if you can make it smarter for me please?

Basically it picks up data and pastes it into a new workbook to create a moment in time report.

In order for it to then go back to the original workbook and tidy everything back up and essentially reset it for the next time, my code uses the below example to go back to the original workbook via its name. Whilst this does work it also means that if the users changes the name the code needs to be updated. Is there any way therefore where I can get it to go back to the original workbook (which could be in a number of directories) without needing to specify its actual name?

Any advice would be much appreciated.

'=====================================================
'Push to another Workbook
'=====================================================
Sheets("Summary Report").Select
Columns("A:T").Select
Selection.Copy
Workbooks.Add
Cells.Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFormattingCells:=True, AllowFormattingColumns:=True, _
        AllowFormattingRows:=True
ActiveWorkbook.UpdateLinks = xlUpdateLinksNever
'need to refer back to the original Workbook - NEED TO CHANGE THE NAME FOR EACH WORKBOOK !!!
'--------------------------------------------------------------------------------------------
Windows("Project Workbook Template V6.5.xls").Activate

 

[Eurekaonide]

having another thought on this, do you think it would be appropriate to have a text type of dialogue box that the administrator / user could type the new name of the workbook and this then changes the code to update the name??

What I'm trying to do is make non VBA users life easier when changing or administering this workbook without having to worry about going into the depths of the code.

 

[CharlesChuckieCharles]

why not just do a SAVE AS with format(now(),"yyy_mmm_dd_hh_nn_SS") stuck on the end of the current workbook name
if you haven't saved the original then it won't need clearing up?