Create multiple sheets in new workbook based on credentials in source workbook

Hi everyone,

I was wondering if anyone could help with the code to do the following:


1. Start a loop going row by row through Sheet A of workbook (A)
2. For each column between 179 and 201 with a step of 2, if there is a value in it, copy a sheet (B) in Workbook A to a new Workbook (B).
3. Rename this new Sheet B in workbook B as the value in the column
4. Go to the next step/column in that row and if there is a value there, check to see if there is not already a sheet in the new workbook (B) that is under that name. If not then create a new sheet in workbook B named with that value
5. Commence the next row of Worksheet A if there is anything there and repeat step 2 onwards if there is.

The purpose of this is to create an invoice for a group of suppliers. However, if the supplier invoice has already been created obviously I don't want another invoice created. Is there any limit to how many sheets one can have in Excel 2007?

Many thanks in advance.

In point 2, you have mentioned column 179 to 201, is it columns or rows?

Also, can you please attach a spreadsheet?

Hi there,

Columns. Although I'll simplify things a little.
Say I've got 6 solumns from A to F and 5 rows.
I need to begin with row 1 and look in each column of that row.
Open a new workbook by copying a sheet called ("Invoice Sheet").
Rename the new sheet in the new workbook by the value found in that column.
Search the next column in that row and paste the same sheet ("Invoice Sheet") into the new workbook but only if the value (sheet name) is unique and has not been found before.

Its a lil complicating still. Please post a sample workbook so we can try and help you out.

Ok here you go.

It's a simplified version but the principle is exactly the same as my original post.
One row at a time, and going through columns in steps of 2, i.e. 1, 3, 5, 7, the supplier code needs to be checked against all the newly created sheet names in new Workbook B. If there is a match then there is no need to paste "Invoice Sheet" to the new workbook. If it doesn't exist then "Invoice Sheet" needs to be pasted to the new workbook object (any position will do) and renamed with the value it is currently at in the column of Workbook A.
Sorry if this is confusing.

can anyone help?

Yes that's right. Thanks for your help so far. I'm thinking about 3 loops need to run. The first one to cycle through each row. Second to cycle through each column in that row. Third to cycle through all the sheet names in the new workbook created so far to see whether it already exists or not.

One thing to note is that not all columns are populated. Some have more than one supplier code and some don't.

Yes i have done the same thing but facing some probs. Am working on them

I have created this code but its erroring out when i have a sheet with the same name, even though i have an error handler. Maybe someone can help us just modify this a lil bit and it should work for you.

Please Login or Register  to view this content.