Split Macro - add formulas to copied sheet

I have created a macro that splits into files based on a column on the spreadsheet. In this same macro I'm also copying an additional spreadsheet (another tab) from the same workbook into the new workbook that is created. Is there a way that I can populate some of the fields on this new worksheet based on values in the new spreadsheet - the contact name will always be the same on each file and is in column B. then I would like to sum up all the values in column S and add that to this new sheet that is added.

Here is my code.

Please Login or Register  to view this content.

SampleInvoiceInfo.xlsx - is the main excel workbook that I'm running the macro against. There will be a new Invoice excel workbook each month
SampleInvoiceInfo_(column A value).xlsx

When the macro runs it will look at the data that I have specified in the user form:

Which Column = A
How Many Header Rows? = 8
Name of Addl Tab to copy (leave blank to ignore) = InvoiceConfirmation
Where to save the file = full path

The macro splits into separate files based on the column I specify and which header row this value is in - so in this instance I specified Column A which is the campus and header rows = 8.
The new Excel workbook name will include the value in Column A in the file name.
It will also include all header rows in addition to the specific transaction rows for that campus in the new file.
It will save to the location I specify.

What I would like added is after it splits by column A (campus) I would like the values from the new sheet created (Sheet1) populated on the InvoiceConfirmation tab.
I would also like the values summed in the total amount column and placed where it needs to go
And the requestor name from the file added to this tab as well in the appropriate place.
I tried to add a formula on the actual main spreadsheet to look at Sheet 1 but that wasn't working so I removed. Maybe I need to do the formula a certain way.

Any help you can give would be greatly appreciated. Also if you think the macro be made more efficient as well that would be great! When the macro is run there could be thousands of rows of data that it needs to sift to and there could be up to 13 campuses that it will be splitting by.

Thanks!
Maria

I forgot to add that I actually added a few new variables to the user form. Here is the new code.

Please Login or Register  to view this content.

Originally Posted by Richard Buttrey

Hi and welcome to the forum.

Just a thought but I suspect that code could perhaps be simplified considerably

Whenever you find yourself creating looping code (like your Do...Loop) stop and think is this really necessary? Most of the time it isn't. Loops are inherently slow when they have to jump back and forth between VBA and the Excel application - there's a big time overhead at each pass through the loop. That said you don't change the value of the Done variable which remains at zero so it's not clear why you have a loop at all.

Why not upload the workbook and explain exactly what should happen after the macro has processed. Show us what you start with and manually add the result you expect to see.

Hi Richard,
I uploaded a workbook and the sheets of what I expect after the split files. Do you have any suggestions?
Thanks!
Maria

Possibly you could adapt this macro to your userform...

Please Login or Register  to view this content.