Excel 2003 Workflow/Macros

[Guest]

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 (it’s
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

 

[Guest]

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.

 

[Guest]

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