I have a large spreadsheet that is a report generated by a program that I routinely split by hand into many smaller
spreadsheets which are saved under a filename that comes from data on the spreadsheet. The format of the large spreadsheet
is not mutable as it is generated by another program.
I want to be able to have a macro that will take a
spreadsheet like "large spreadsheeet Example.xlsx" and create many smaller spreadsheets like:
I'm attaching an example large spreadsheet and an example from the middle of the large
spreadsheet of what the smaller sheets end up looking like once I have cut and pasted and saved them. The name of the file
is important and is the value of "Variable Namexxx" in the spreadsheet (it's just a persons name in reality), so the name of
a file above would look something like "John Smith.xlsx" or "Sam Jones.xlsx". Whatever is on the spreadsheet.
acutal spreadsheet is many thousands of lines long and contains 100's of "variable namexxx", so once done, you will have a
directory full of 100's of smaller files that the macro creates. The amount is variable depending on the run date of the
The large spreadsheet, while it will be considerable longer in real life, will follow the example given.
It will ALWAYS begin as you see it and ALWAYS end as you see it with a variable number of lines in between with many
different "Variable Heading1" type fields on it. The number of rows per "Variable Heading" is not set and can be 100's of
rows long (I have shortened them down for sanity in the examples). The "spacing" that comes before and after the smaller
spreadsheets follows the pattern you will find. If the "Variable Heading1" does not change, then there are two blank rows
and the next "Variable Heading1" row. If this changes to "Variable Heading2" (or whatever the next one is), then there will
be 6 blank rows between them. I'm not sure if this is even helpful information as I think the best source to pick the
beginning row of the smaller spreadsheet is that this row contains the data "Static1:". And the last row that you would
select in the copy fuction would contain the data "Static8". I'm not sure if this is a good delimiting factor, but it is
basically how I do it manually (actually, I'm looking at both this and the color cues as I scroll around cutting and
Also, as you can see in the smaller spreadsheet examples, the formatting of the original large
spreadsheet is kept intact on the smaller ones (same column widths, row heights, background colors, etc...etc...). This
formatting is very important as I have another macro that is used on the smaller spreadsheets to further change them into
their final form which is emailed out the the individuals for them to enter other data on that spreadsheet which is then
collected, further worked on by macros, and finally put back into another large spreadsheet with all the newly collected data
shown from the emailed individuals. I've managed to do all the other macros, but this one eludes me.
taking a look in advance as well as many thanks later...