Add-in to saving groups of worksheets as a single workbook

Hi all

I have another little conumdrum I'm trying to work out.

I'm using one of Ron's scripts as an add-in which I've just amended the output path for.

http://www.rondebruin.nl/tips.htm

This script essentially creates a new workbook from each worksheet in the active workbook.

As I have a workbook of about 500 worksheets, I am trying to group the worksheets opposed to having to split all and rejoin them manually.

Luckily all of my worksheets names are initials with numbers after them (as assigned by excel when merged):

e.g., RH, RH (2), RH (3), RH (4), AG, AG (2), AG (3) and so on.

That said there are some random ones too, but I'm happy for them to be kicked out as individual files.

Anyway I'm pretty sure there is a way this can be added into this existing script by addressing Sheet.Name perhaps but I'm a bit unsure exactly..

Sorted in order (i.e. 2,3,4) would also be a bonus but not essential as they need to manually checked and I have a separate macro for sorting worksheets anyway.

Any input would be much appreciated!!

Cheers

Danny

Please Login or Register  to view this content.

Do you have a list of initials stored in the workbook?

No, unfortunately not. I'd have to use Sourcewb.Sheets.Name in this instance as there is no common cells across all sheets.

I thought about using Case but the script would get too bulky.

I think you need a list of initials, maybe created with the code then loop through the list to create the workbook.

Yeah the initials could be created in the code. i'll try that first. cheers!

Not completely tested but try this

Please Login or Register  to view this content.

Hi Roy

Thanks for your input so far.

Sorry I forgot to mention... some worksheets have 3 characters, e.g. ABR and other just have 2 e.g. AG. The list generated therefore truncates some of these names. I tried changing to (sh.name, 3) in both the create list and copy_worksheet macro but that just produced a list with some duplicates.

When running main and calling the copy_worksheet macro, only one file is generated (containing all the worksheets) with the name of the first worksheet from current workbook . After that I get a Path/File Access Error.

It will be difficult to build a list of names because all the three letter names will appear in the list twice.

Hi again

How about a change of tack on this one..

Say I have 4 workbooks each with the same sheet name in each.

(e.g. WB1.xls has sheet 'AG', as does WB2.xls and so on.)

Is it possible that grab each worksheet with the same name from each workbook and save them together in one workbook? Please note that some sheets will have 3 initiaals instead of 2.

I figure that this script could be modified and looped to work with the copy script perhaps?

Cheers

Danny

Please Login or Register  to view this content.

Just physically copy each sheet to a new workbook if they match, or combine the data on each sheet to one?

That would be easiest, but considering I have 105 sheets x 4 workbooks - I'm having to manipulate around 420~ worksheets effectively. I wish they could be merged, but unfortunately they need to stay as separate worksheets.

At the moment (via macros and userforms) I am just importing these 4 large workbooks into one giant workbook and getting excel sort/add the suffixes (2), (3) etc. Then I am saving each worksheet as an individual workbook after which, a file/sheet listing is produced. This list is then automatically printed to the default printer.

Using the file list I manually have to recombine the sets of files into new workbooks (or 'Packs' as I can them) takes hours though.

maybe you could use a macro to first go through the first workbook and create individual workbooks in a subdirectory. Then use a separate macro to then open each remaining workbook match the sheets to the relevant workbook & copy the data to the end of each workbook's sheet.

Someone here has changed their mind and would rather work from one source, in that case I don't need to match from other workbooks.

In that sense its very easy for me to import all the worksheets into one workbook and create an array to select several sheets which I then bolt more code onto.

I've implemented this so far but the array only works when 4 sheets are present for each person - anything less is 'outside the range'

I'm a bit confused about the whole redim/preserve/erase thing. How could I rewrite this so excel knows to look to check for multiple arrays. Nesting this would be ok.

My four arrays would be.. (and I realise you wouldn't define them like this)

Please Login or Register  to view this content.

Cheers


Danny

Please Login or Register  to view this content.