I just start working with Macros and I chose to use it on a report that I create every week. The only thing is that every week I save it with a different name and chage the date. The macro works only if I edit and change the name of the worksheet. I am assuming there is an easier way to use the macro with different workbooks...Maybe is the way that I am saving it?
Please take in consideration that I am a biginer.
Thank you
If would be helpfull to see the code. But somewhere in the code you have
try
Please Login or Register to view this content.
Please Login or Register to view this content.
Thanks,
Mike
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
Select Thread Tools-> Mark thread as Solved.
I have
Shall I change Windows to Activesheet?Please Login or Register to view this content.
Thank you
You can try this, but without knowing/seeing your whole code im just shooting blind. If this dosent work can you upload your code and or workbook?
Please Login or Register to view this content.
I made the change and it didn't work
This is the code that I have, with the change:
Please Login or Register to view this content.
What line is giving the error? Better yet If you like tell exactly what your doing and I can clean up the code? Are you really wanting formulas in the sheet? Is C:\DMPS\61955cb_2.xls the file you want to open everytime? Can you upload the workbook file with the macro and the 61955cb_2.xls so I can see an test. If its sensitive data just replace with dummy data but leave the data structure the same. Ex: Headers sheet names.
Yes, the C:\DMPS\61955cb_2.xls is the file that I will always open. Is a download that will always over overwrite. I am attaching this file and I am also attaching the altered report where I bring the information in. I mainly have to count how many heads have overtime, how many hours and the value, per 5 categories. The macro would work fine IF I always edit the name of the worksheet I am working in. And every week is saved under a different date. So I would like to learn how to build up macros that I can use in a spreadsheet, no matter what the name is. I do the same reports weekly, but I save them under different names all the time. I would like to solve this issue because it’s something that affects ALL the macros I build for all other reports.
Thank you
61955cb_2.xls
Week 09-08-12.xlsx
Im a little lost as to exactly what to happen. The reason is, the start of your Code? Activecell.select, workbookopen sheets select week workbook active activecell select. So its had to know whats going on Is there a certain cell that you click on before running the macro? Is there a sheet I need to be on before running the macro? If you can tell me What Im to look for in C:\DMPS\61955cb_2.xls. Ex: What Columns. Then where is the results to be place at in the Week workbook. Ex: What column what rows What sheets?
Please Login or Register to view this content.
If you put in detail the steps you if you were to do this manually. Like sum up what column if .... And put that total where?
When I run the macro I am supposed to be already in the spreadsheet, select the cell where I start bring the information (in my case tab 3 of 'Week 09-08-12', cell L46). Then the macro is opening the other file and stops because doesn't recognize where to bring the info, since the workbook is saved under a different name. If I change that name, then the data is being calculated and is populating my little table.
My criterias are on Column E and needs to total values on columns K and L and to count the values on L, according to the formulas used (SUMPRODUCT). Not sure if I make myself clear and what other information is needed.
Overall, I need to know how can I create a macro that I can use in workbooks saved under different names.
What I need to do and how I am doing it is part of this thread: http://www.excelforum.com/excel-form...criterias.html
Ok those 3 values, where do they go in the Week worksheet?
L46 is a count of employees that did OT coded between 4000 and 4037, M46 is the hours of OT for the same category and N46 is the amount of money. L47:N47 is the same thing for those coded 5010-5016, L48:N48 is for those coded 5000, L49:N49 is for 5020-5022 and L50:N50 is for 5030-5032.
@Amber
See if this helps. Sorry took so long I'v been busy all day. I fixed up your code some. Also included a function to check if the workbook is already opened. If it is then we will leave it open if not we close it after were done with it. See attachment Ver1
And heres another way. I like this way. It will query the sheet like a database. A lot faster. See attachment Ver2Please Login or Register to view this content.
You can run the macros by clicking on your little man with the hard hat.Please Login or Register to view this content.
Ver1.xlsm
Ver2.xlsm
Last edited by mike7952; 10-07-2012 at 08:11 AM.
Sorry I answer late as well.
With the first version I get a message: Error#: 9 Description: Subscription out of range.
With the second version it gives me an error and highlight in yellow this row: With ThisWorkbook.Worksheets(shDest)
I don't know how to fix these and where the problem is.Please Login or Register to view this content.
As a rule (if there is such a thing) how can I save a macro to work in any file, no matter the name is saved under? Is that something I have to add in code? Is it the way I save it? I want to be able to build by myself, recording, any macro and be able to use it, even if I change the name of the file.
Thank you
where are you saving the Macro? What is the name of the sheet that we want to add the totals to?
Edit: shDest is a Const at the top of both codes. shDest = "3". change 3 to the sheet name in your actual file.
Last edited by mike7952; 10-09-2012 at 09:35 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks