Excel spreedsheet
I have an excel spreadsheet that I have saved as a template. I input people's data in to the worksheet and save it as that persons name. I have a macro on the template that I need to run at the end of each month but it will not work after I save the worksheet under the persons name. how can I get around this problem. Any help would be great. I will attach the macro if that will help. Thank you
Macro.txt
Macro.txt
Russell Fox
Make sure you are saving the renamed workbook as a macro-enabled workbook, xlsm, rather than the standard xlsx.
Hello,
looks like you run Excel 2003 or earlier, so the only file extension you need is .xls, both for macro and non-macro files.
The code you posted names several files verbatim. It looks like it has been created by the macro recorder. It opens a file called
T:\Occupational Therapy\Therapy.xls
and then assumes that another file called
John-Smith 03-08-1944.xls
is already open in another window. Finally, the file is saved in
T:\Occupational Therapy\Export Files\140544.xls
This macro will not magically adjust if the file name is renamed. If you need a dynamic solution, you need to make the code dynamic.
Can you explain in words what the macro is supposed to do? Then we can help construct it to work with other files.
cheers, teylyn
looks like you run Excel 2003 or earlier, so the only file extension you need is .xls, both for macro and non-macro files.
The code you posted names several files verbatim. It looks like it has been created by the macro recorder. It opens a file called
T:\Occupational Therapy\Therapy.xls
and then assumes that another file called
John-Smith 03-08-1944.xls
is already open in another window. Finally, the file is saved in
T:\Occupational Therapy\Export Files\140544.xls
This macro will not magically adjust if the file name is renamed. If you need a dynamic solution, you need to make the code dynamic.
Can you explain in words what the macro is supposed to do? Then we can help construct it to work with other files.
cheers, teylyn
ASKER
I have a template that every month we save for every patient in the office under the name of that person. Every day there is data added to that worksheet. At the end of the month i copy the end totals and paste them to a new work sheet and save that new work sheet under the number of the patient. I have the macro open both worksheets and copy the data from one to the other i have it copy the patients number and save the second worksheet under that number. The macro works great for the first patient but when i change the patient it stops at the line John-Smith 03-08-1944.xls. Is there a way to have it runn from all patients every when the worksheets name changes all the time.
Thank you for your time
Thank you for your time
ASKER
Just checking to see if anyone can help me with this problem. I am not well versed with writing macros or any VBA stuff. Thank you for you time.
ASKER
This is my workbook “Template.xlsx”. I need to open Workbook “Data.xlsx” and rename or save it at the end with the person number in cell G3. I then need to copy data from cell H6,H7,H9,H10,H12 and H13 and paste it to cells J5 to j11 in new workbook “451535”. The data in H6 on is in this format “=SUM(C6:G6)” and needs to be changed to the value of the sum so it can be copied over. I am going to be using the 2 workbooks about 60 times each month for the 60 people we will be working with so the macro needs to names and data each time. Is this possible. Thank you.
Template.xlsm
Template.xlsm
What version of Excel are you using? The code you posted above is for 2003 or earlier, but the files you post are 2007 or later. Please clarify. This is important.
What is the structure of the Data.xlsx file?
If you use a template, why don't you use a real template, i.e. an xltx or xltm file, which will create a new file when it is opened?
What are you doing with over 60 workbooks created each month? That looks like a very inefficient way of handling data. Have you considered using a database approach instead of individual files with identical tables?
What is the structure of the Data.xlsx file?
If you use a template, why don't you use a real template, i.e. an xltx or xltm file, which will create a new file when it is opened?
What are you doing with over 60 workbooks created each month? That looks like a very inefficient way of handling data. Have you considered using a database approach instead of individual files with identical tables?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you for your help on this. This worked great for me. Thank you again.