Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2003 Workflow/Macros
I have a spreadsheet sent to me by email attachment on a monthly basis. Each
month, I open the spreadsheet, delete 37 of the original 58 columns (its always the same 37 columns) and then copy and paste the remaining 21 columns x however many rows (I exclude column headings) into a second spreadsheet. In pasting the cell range, data is always appended to the bottom of the sheet, starting in Column A. The second sheet has edited column headings for ease of reading. The following month, the whole process is repeated again. Has anyone used a macro to do this? If so, any pointers or advice about the workflow would be much appreciated. Amanda |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2003 Workflow/Macros
You're about to venture into macro territory, so be prepared. What you want
to do is record a macro, but certain things have to be the same month after month, so you have to set up for the macro properly and record it properly. First, open both files, and switch to the "second" one, where you append the data, because this is where you want your macro to live. Then start with Tools / Macro / Record Macro, and at the next dialog box, give it a better name than "Macro1", give it a keystroke, and make sure "Store macro in" says "This workbook". You'll then get a tiny toolbar with a Stop button -- you're recording now. Use Ctrl-F6 to switch to the first sheet, delete the 37 columns, select the Nx21 area, use Ctrl-C to copy, then use Ctrl-F6 to switch back. Select the sheet where you'll paste, and hit Ctrl-Home, Ctrl-down-arrow, and then down-arrow with no Ctrl. This should put you in column A's first blank cell -- use Ctrl-V to paste. At this point you should click the tool to stop recording, because you still have the option of closing your files without saving in case anything got messed up. This means your new macro and its keystroke will or won't be saved. If you like the macro, save the second workbook. From this point on you then start the same way as when you recorded it: open both files, and make sure the second one is active. To delete the macro, go to Tools / Macro / Macros, single-click the macro, and click Delete. -- * Please click Yes if this was helpful * Andy Smith Senior Systems Analyst Standard and Poor''s, NYC "Birmangirl" wrote: I have a spreadsheet sent to me by email attachment on a monthly basis. Each month, I open the spreadsheet, delete 37 of the original 58 columns (its always the same 37 columns) and then copy and paste the remaining 21 columns x however many rows (I exclude column headings) into a second spreadsheet. In pasting the cell range, data is always appended to the bottom of the sheet, starting in Column A. The second sheet has edited column headings for ease of reading. The following month, the whole process is repeated again. Has anyone used a macro to do this? If so, any pointers or advice about the workflow would be much appreciated. Amanda |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2003 Workflow/Macros
Hi Andy
Thanks for your prompt response. Yes, this all makes sense to me - I have used macros before, but the way I was thinking about automating my routine was far more complicated than your answer, which was why I asked about workflows :) I'll try your solution tomorrow when I can work with my real data... Cheers, Amanda "Andy Smith" wrote: You're about to venture into macro territory, so be prepared. What you want to do is record a macro, but certain things have to be the same month after month, so you have to set up for the macro properly and record it properly. First, open both files, and switch to the "second" one, where you append the data, because this is where you want your macro to live. Then start with Tools / Macro / Record Macro, and at the next dialog box, give it a better name than "Macro1", give it a keystroke, and make sure "Store macro in" says "This workbook". You'll then get a tiny toolbar with a Stop button -- you're recording now. Use Ctrl-F6 to switch to the first sheet, delete the 37 columns, select the Nx21 area, use Ctrl-C to copy, then use Ctrl-F6 to switch back. Select the sheet where you'll paste, and hit Ctrl-Home, Ctrl-down-arrow, and then down-arrow with no Ctrl. This should put you in column A's first blank cell -- use Ctrl-V to paste. At this point you should click the tool to stop recording, because you still have the option of closing your files without saving in case anything got messed up. This means your new macro and its keystroke will or won't be saved. If you like the macro, save the second workbook. From this point on you then start the same way as when you recorded it: open both files, and make sure the second one is active. To delete the macro, go to Tools / Macro / Macros, single-click the macro, and click Delete. -- * Please click Yes if this was helpful * Andy Smith Senior Systems Analyst Standard and Poor''s, NYC "Birmangirl" wrote: I have a spreadsheet sent to me by email attachment on a monthly basis. Each month, I open the spreadsheet, delete 37 of the original 58 columns (its always the same 37 columns) and then copy and paste the remaining 21 columns x however many rows (I exclude column headings) into a second spreadsheet. In pasting the cell range, data is always appended to the bottom of the sheet, starting in Column A. The second sheet has edited column headings for ease of reading. The following month, the whole process is repeated again. Has anyone used a macro to do this? If so, any pointers or advice about the workflow would be much appreciated. Amanda |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Designing a spreadsheet to track workflow | Excel Worksheet Functions | |||
Designing a spreadsheet to track workflow | Excel Worksheet Functions | |||
Excel 2003 and Macros | Excel Discussion (Misc queries) | |||
Excel 2003 macros | Excel Discussion (Misc queries) | |||
excel 2003 - macros & buttons | New Users to Excel |