Editing Macro that splits into sheets with data repeated at top

[DLSmith]

Good afternoon,

I have a macro that splits sheets based on manager name - I am now trying to add a "tally" box to the top of each sheet to track their "budget" spending.

I'm not really good at explaining so I have attached 3 samples.

1. Rawfile is the data as it would exist pre-split
2. Splitworking is the way the file currently works and the macro that I'm currently using (I didn't write it, it was posted back in 2011 and has worked great the hundreds of times I've used it so thank you again to the original author!!!)
3. Results is the way I would like the file to look post split

The column highlighted in yellow would be blank on each sheet for the manager to enter their increase % - and ideally would pull on to the main(combined pre-split sheet) and the totals at the top in the green "tally" box would automatically update on each sheet based on the size/totals of that sheet and also pull to the main (combined pre-split sheet).

I could just cut/paste the green tally box to each sheet after the split - but there is 200+ sheets and I do this several times a year for large groups of people -- so any automation ideas are greatly appreciated.

Thanks in advance for any assistance or ideas you may have!

[AlphaFrog]

Try something like this. Only the code in red was changed\added.

The top 8 tally rows are copied to each new sheet and then the filtered data is copied starting from row 9.

Please Login or Register  to view this content.

[DLSmith]

Thank you!!!! That worked beautifully for splitting and adding the rows at the top.

Can I split by another field instead of A? For example if I wanted to split based on Job Title - but still leave Job Title in column E would that work?

I tried changing the A9 to E9 and it did split, but it did not include columns A-D.

I really appreciate the help!

[AlphaFrog]

See this part of the code...

Please Login or Register  to view this content.

...Column E would be 5

[DLSmith]

Got it - thank you. I guess that was a pretty "duh!" moment. I shouldn't have changed the others from A9 to E9 - I should have just changed that to 5 (or whatever the corresponding column # is).

The only thing that doesn't seem to work is pulling the changes made in column N (the yellow one) from the separated sheets back to the pre-split file. I've tried using a vlookup using the Employee ID number but I don't know how to link it to multiple as of yet unknown sheets. I guess I could set something up post-split but if there's 100+ tabs that would be pretty labor intensive.

Is there a way to use an array or something similar? Or is there a way to re-combine the sheets post-split?

Thanks again for your help!

[AlphaFrog]

I'm not sure I fully understand but here's a guess.

Put this in N10 on the Full Sheet and copy it down the column.

=INDEX(INDIRECT("'"&A10&"'!M:M"),MATCH(C10,INDIRECT("'"&A10&"'!B:B"),0))

It looks up the employee's full name in cell C10 MATCH(C10 on the Supervisor's named sheet from A10 (INDIRECT("'"&A10&"'!B:B") in column B. It returns the value from column M of the Supervisor's sheet INDEX(INDIRECT("'"&A10&"'!M:M").

[DLSmith]

Thank you -- I got a little lost...even though you took the time to try and explain, but was finally able to figure out my mishap -- it was because I've already rearranged things and forgot that I had changed the A9 in the previous version which I didn't need to do.

The latest works great and I really appreciate the time you took to explain what the steps were doing so I can use it for other applications when they change the requirements on me.

The only thing (I've said that before, right) that doesn't seem to have worked from the split is post split it is not keeping my formulas in columns N and O.

My guess it is something to do with the fact that in the macro I'm pasting values and formats but not formulas -- maybe in this section:


'Copy the visible data to the worksheet
My_Range.SpecialCells(xlCellTypeVisible).Copy
With DestRange
.Parent.Select
' Paste:=8 will copy the columnwidth in Excel 2000 and higher
' Remove this line if you use Excel 97
.PasteSpecial Paste:=8
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
.Select
End With
End If

Any suggestions?

Thanks again..again.

[AlphaFrog]

You're on the right track.

Change these two lines...
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats

To these two lines...
.PasteSpecial xlPasteAll
Range("M10:M" & Rows.Count).ClearContents 'Clear formulas from column M

[DLSmith]

You fix something for me ... I break something else =) Sorry....

That did fix my issue of the formulas within the worksheet - but seemed to mess up my vlookups I have to other sheets that I didn't have in my original sample. I could do a copy paste values on those look up fields prior to splitting as an easy solution -- but if there's a better way to handle it through the macro, let me know or maybe I just need to do something within my vlookup to 'lock' or 'anchor' it to the appropriate sheets prior to splitting?

Thanks again -- and thanks for sticking with me!

[AlphaFrog]

The macro could replace the VLOOKUP formulas with their result values prior to splitting. I don't see any VLOOKUP formulas in your example sheets, so I don't know what you want to do.

As an example, this will replace the formulas in column P with their result values.
Range("P:P").Value = Range("P:P").Value

[DLSmith]

Thanks for the response - I haven't had a chance to test yet, but hope to by end of week -- just wanted to acknoweldge and thank you again for the response!