| x404.co.uk http://www.x404.co.uk/forum/ |
|
| Excel : Macro to run a set of tasks, but on different files? http://www.x404.co.uk/forum/viewtopic.php?f=4&t=3362 |
Page 1 of 1 |
| Author: | bish [ Tue Oct 13, 2009 3:38 pm ] |
| Post subject: | Excel : Macro to run a set of tasks, but on different files? |
Okay, Each morning I have to do a back order analysis. I have to hide this column, hide that column, fill this column, fill that column, resize these columns, create these sheets, write these formulas... each day, the same thing, the same formulas, the same everything. Now, the only thing is, each day there is a new file. With diffrent information. So if you can see what I mean, I need to file to be automated, to save about an hour of my day. Any ideas on acheiving this? |
|
| Author: | forquare1 [ Tue Oct 13, 2009 3:51 pm ] |
| Post subject: | Re: Excel : Macro to run a set of tasks, but on different files? |
Don't ask me how, but can't you attach a macro to the Normal template (or any other template for that matter)? Does this apply in Excel or is this just a word thing? As long as you use the said template, the macro will be included... |
|
| Author: | bish [ Tue Oct 13, 2009 3:53 pm ] |
| Post subject: | Re: Excel : Macro to run a set of tasks, but on different files? |
This is what I'm trying to wrap my head around... A report is run at 6pm in the evening. I come in in the morning and analyise the information, and segment all the imformation into sheets. So because I don't run the report, I can't tell it how to be set up. I also can't loose any information, so it's very very vital to have things in tact. Ah I'm going out of my mind. |
|
| Author: | AlunD [ Tue Oct 13, 2009 4:01 pm ] | |||||||||
| Post subject: | Re: Excel : Macro to run a set of tasks, but on different files? | |||||||||
Unless you can control the file creation you can't even import it into a template spreadsheet......... sorry. As there is a quantifiable saving get mgt to issue a directive that the report will be created in the following format / using the following template blah blah blah. 
|
||||||||||
| Author: | jonlumb [ Tue Oct 13, 2009 6:18 pm ] |
| Post subject: | Re: Excel : Macro to run a set of tasks, but on different files? |
Write your macro to a master file. When you come to edit your new workbook, simply have your master file open in the background and you'll be able to access the macro. |
|
| Author: | big_D [ Wed Oct 14, 2009 6:38 am ] |
| Post subject: | Re: Excel : Macro to run a set of tasks, but on different files? |
Correct Jon, The Macro should even be able to prompt for the file to be opened, then work on it... Or if the report file is in a specific location and has a specific naming convention based on date, for example, the name can be calculated and automatically opened and then manipulated. One of the first Excel macros I wrote, back in 1988, opened around 200 separate worksheets, one after the other and extracted timesheet information for each employee and added it to a new master table. |
|
| Author: | bish [ Wed Oct 14, 2009 7:33 am ] |
| Post subject: | Re: Excel : Macro to run a set of tasks, but on different files? |
Hmm, seems like big_D can help... What do you propose i do? |
|
| Author: | jonlumb [ Wed Oct 14, 2009 9:25 am ] |
| Post subject: | Re: Excel : Macro to run a set of tasks, but on different files? |
What's your level of macro writing skill like? For a lot of stuff, simply using the Record Macro function is the most straightforward option. It cannot do anything conditional, but if you are doing the same thing to your spreadsheet every morning then that may well not be an issue. Things like pasting formulas are easily covered by the Record function. |
|
| Author: | bish [ Wed Oct 14, 2009 9:33 am ] |
| Post subject: | Re: Excel : Macro to run a set of tasks, but on different files? |
Jon, I know quite basic VB, nothing like amazing, just the basics. I'd probably use the record macro for this sort of thing, because it kind of kills 2 birds with one stone! Problem is, recording the macro is fine, but then how do I get the macro to run on different, seperate files? |
|
| Author: | jonlumb [ Wed Oct 14, 2009 9:38 am ] | |||||||||
| Post subject: | Re: Excel : Macro to run a set of tasks, but on different files? | |||||||||
Save a copy of the file you are working on, and record the macro. We'll call it Master.xls Then, in subsequent times, open the file you want to run the macro on (edit.xls), open Master.xls and in the Macros box in edit.xls you will find the macro stored in Master.xls, which you can then run. |
||||||||||
| Author: | forquare1 [ Wed Oct 14, 2009 10:01 am ] | |||||||||
| Post subject: | Re: Excel : Macro to run a set of tasks, but on different files? | |||||||||
After not writing Macros for three years I suddenly had to write a load of them over the summer. Google is your friend here 
|
||||||||||
| Author: | big_D [ Wed Oct 14, 2009 11:09 am ] |
| Post subject: | Re: Excel : Macro to run a set of tasks, but on different files? |
Record the Macro from a new worksheet, open the worksheet you want to edit, make the edits etc. and save the changes. Then go back and use an Input box for the file name and substitute the filename in the open and save commands for the one you got from the Input box. |
|
| Author: | AlunD [ Thu Oct 15, 2009 7:01 am ] |
| Post subject: | Re: Excel : Macro to run a set of tasks, but on different files? |
Guys unless the report is in a set format, the same every day I don't see how the macros will work. You can tell a macro to get information from a set location or very limited flexibility but if the information isn't where it expects it ......................... First step get control of the report creation and the format its out put in. |
|
| Page 1 of 1 | All times are UTC |
| Powered by phpBB® Forum Software © phpBB Group https://www.phpbb.com/ |
|
