| Forum Home | ||||
| Press F1 | ||||
| Thread ID: 15243 | 2002-02-01 22:34:00 | Excel macros, HELP | Guest (0) | Press F1 |
| Post ID | Timestamp | Content | User | ||
| 33942 | 2002-02-01 22:34:00 | I am currently setting up an excel spreadsheet to record weekly sales figures & need some help with a macro. At present I have a workbook with several sheets, sheet 1 is a blank printable form with the date & headings on the left side, i.e. ?Number of phone calls, number of visits, Sales $ etc,? I enter the sales figures & the form is emailed to my boss, I clear the figures & it is ready to send next week. Sheet 2 holds all the weekly figures & is arranged with the headings down the left side & the dates of each Friday in the year in columns out to the right. Each week I manually enter the sales figures into this sheet in the column under that weeks date. The charts & graphs comparing sales to targets etc are completed from these figures. I want to automate this process, so I only have to enter the figures once on the sheet that I email to my boss. I want to know if it is possible to write a macro that, A) emails sheet 1 to my boss, then B)moves the figures entered on sheet one to the corresponding column on sheet 2, i.e. figures for the week ending 8/2/02 on sheet 1 are copied to the column under the heading 8/2/02 on sheet two, then C)clears the figures from sheet 1 so it is ready for use next week & D) saves & closes the workbook. I?m using Excel 2000. |
Guest (0) | ||
| 33943 | 2002-02-02 03:55:00 | Macros record the keystrokes that you do so.... STEP 1: MAKE A BACKUP! STEP 2: Open the backup copy, start recording a macro and step your way though it. STEP 3: When finished, stop macro STEP 4: Run macro STEP 5: (Optional) Swear, delete macro and repeat steps 2 to 4 |
Guest (0) | ||
| 33944 | 2002-02-03 22:48:00 | Heather's suggestion is a good starting point. You wil find though that the resultant code (press ALT F11 to view it) may not work for the next round. e.g. This week you put data in Col G and next week you want it in Col H - you will find that the recorded macro uses absolute references and will need editing to allow it to become generalised. The help (F1)for Excel macros is quite good if you are familiar with Basic or VBA. There are examples for most constructs that can be used to get ideas on how to code. It is worth persisting as once you have written your macro(s) it will save lots of tedium for those regular jobs. jt |
Guest (0) | ||
| 1 | |||||