I need to write a macro. I used to know a little VisualBasic, but my skills are really rusty; and I think this task is beyond the point-n-click macro recorder in Excel. Here are the requirements:
I’m starting with a block of data in the spreadsheet, sorted by column 1. For each change in the value in column 1, I’d like to take all the rows that have that value and put them in a new separate worksheet within the same workbook. In other words, break up all values of column 1, into their own special worksheet. There are a variable number of rows in each block, and I don’t know how many different blocks (ie, values of column 1) there will be.
And when I’ve created this string of worksheets, I’d like to replicate the formatting and graphics from the original worksheet onto each of the new ones – without knowing how many new worksheets I’ve created.
You’re right this won’t be readily doable with the macro recorder.
Assuming you’re really going to program this, you’ve got the sequence backwards. For each block of column1 values, you first want to create a worksheet contaiing all your formatting, chart definitions, etc., then paste all the relevant values into it.
I’m wondering if you haven’t jumped the gun and made a rash decision on what you’re trying to do. Normally, slicing data the way you want to is a poor idea; it’s typically better to leave the data in one big table and just select what you want to display a subset, rather than take it apart into separate spreadsheets.
The simple way to do that is using Data | Filter | Autofilter which may do what you want.
Create a blank row above your data, select the blank row + all data rows by all data columns and choose Autofilter from the menu. You’ll see dorpdown triangle appear in the cells of your blank row. You can select any value of column1 from the dropdown above column1. You can also contruct formulas to define what to display and what to hide.
That’ll change what’s displayed to show only the relevant rows, and will also restructure any related charts to only display those values. You can also copy the displayed rows any you won’t get any of the invisble rows.
If that doesn’t work, let us know and somebody will whip up a suitable macro to generate all those separate sheets.
Autofilter’s a darned good idea (I know how to do it, just hadn’t thought of it–I was locked into the idea of physically separating the data, rather than just hiding what I don’t want). I’ll run it by the user (this is real-life professional work-related) tomorrow and see if he’s cool with it.