Logo 
Search:

MS Office Forum

 Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Creating a new file that keeps the macro that creates it

  Asked By: Maria    Date: Jan 27    Category: MS Office    Views: 958
  

I have a macro that performs some comparisons between today's data (on Sheet 2
in a file) which I pull off the web at the end of the day, and yesterday's data
(on Sheet 1 in the same file). It determines and earmarks what's new today that
was not there yesterday and then puts that on Sheet 3 in the same file.

Then it creates a new file. It copies today's data (Sheet 2) into the new file,
and puts it on Sheet 1, so that tomorrow it will be seen as yesterday's data,
and I can repeat the process.

My problem:

When I create the new file, the macro doesn't carry over, and I have to manually
copy and insert it. Is there any way I can have the macro itself be part of the
new file in an automated way?

Share: 

 

1 Answer Found

 
Answer #1    Answered By: Perdita Lopez     Answered On: Jan 27

Two possibilities:

1) Put your macro  in its own workbook, separate from the data  worksheets. You
may need to tweak the code to operate on the active workbook, not ThisWorkbook.
You could even save it as an Excel add-in.

OR...

2) After your macro has done its retrieval and analysis work, and saved the
workbook as today's file, have it Save As again with the name for tomorrow's
file. Then rename the sheets so that Sheet2 becomes Sheet1 (you'll have to
rename Sheet1 first) and clear every sheet  but the new Sheet1.

 
Didn't find what you were looking for? Find more on Creating a new file that keeps the macro that creates it Or get search suggestion and latest updates.




Tagged:        

 
 
 
 

Related Post