Create new sheets in existing workbook based on columns

[NMullis]

Good morning,

I have attached an example to hopefully help demonstrate what I am after. On the Total by Account tab I have multiple columns with different account names and their sales.
1. I would like to create a macro to place each of these accounts and their respective sales onto separate tabs. The actual file contains 117 columns of accounts.
2. As you can see by my example Acct 1 and Acct 2 tabs, I will also need to copy and paste ranges D7:H101.
3. I will need to create the same formulas on each tab which will reference another sheet in the workbook 'Americas (incl NSI)' as well as the sheet it is now on. I have placed this formula as text, which will need to autofill H8:S101, along with subtotals.
4. Finally, I need the tab to contain the name from the respective cell in row 2.

Please feel free to request more information. I tried to detail this as well as I could and give examples without giving away confidential information.
FYI - I am currently in Excel 2003, however will be upgrading to Excel 2010 in the next week.

Thank you,
N

[carsto]

2. As you can see by my example Acct 1 and Acct 2 tabs, I will also need to copy and paste ranges D7:H101.

On Total by Acct D thru H are 5 columns with F being empty
On Acct 1 this same data consumes A thru F, or 6 columns with C and E being empty
Is this truely your intent?

3. I will need to create the same formulas on each tab which will reference another sheet in the workbook 'Americas...

do the formulas exist on Total by Account?


AND:
What are the April thru Mar Column Headings on Acct 1 all about? Is it your intent to run this macro monthly and place the Account Total data into the correct column each month?

If I don't reply promptly, it's because I may not be able to work on this again until Monday.

[NMullis]

2. I deleted some of the data for confidentiality. Truly there will be static data on the Total by Account tab, columns F and H (erroneously deleted). Columns A through C will have a vlookup formula that I haven't added yet.

3. As of right now the formulas do not exist because I am not sure if it is easier to have them on the Total tab and then try to copy them with the other data and then change for each sheet or to have that written into the macro.

The formula in cells H8:H12 will need to fill for each month, each PC. This workbook will function to spread sales plans for the year, therefore the macro will run only once at the beginning of our fiscal year.

I hope that answers everything clearly enough. If not, please feel free to question further.

Thank you for looking at this, carsto!

[carsto]

OK. I will work on this Monday unless someone solves it over the weekend. (Sorry I just don't have time this weekend.)

117 sheets with almost 1,000 linked cells per worksheet is HUGH! You may have a memory issue.

1.
April formula is =IF('Americas (incl NSI)'!$Q9=0,0,('Americas (incl NSI)'!$AJ9/'Americas (incl NSI)'!$Q9)*'Acct 1'!$F8)
If you copy this formula across for May thru Mar, you will get the same answer in every month.
Is this correct? If so, I would recommend placing this formula in April and then let May's formula be =I8 (or April's result). That way you have 1/12 of the cells linking to a second workbook with an IF statement; your calculation time will be greatly improved.

2.
Is the Americas workbook going to be open while this macro is running? Once the data is transferred, is it static? If so, I would recommend after the creation of each sheet to copy and paste special the values to eliminate the formulas and further reduce the calculation time.

[arlu1201]

Can you provide a sample of the Americas workbook and tell us which cells / ranges from the workbook are linked to your current workbook?

Also, if you intend doing this via formulae, it will take a lot of time to calculate and memory resources too. Instead, we can help you create a macro that will copy the values and paste special them in your current file. This will be much faster and not memory intensive as well.

[rvasquez]

Hello there,

Attached is a spreadsheet I created based on the one you supplied. The first three tabs are the ones that you supplied on your original spreadsheet. The last tab (colored in yellow) is the tab I created. Hopefully this will help a little. I have included comments in cells A1 and D1 of the Acct3 worksheet to help you understand what I have done.

Thanks,

rvasquez