Move rows macro

Folks,
I've been building a workbook that is designed to be as automated as possible since the client think a macro is a fish.
I'm going to break this into different questions, with this being the first. Now that I've set the stage I have attached the workbooks.
I'm going to do my level best in explain this.
1. Save the two attached files. The Append file will add to the Cost allocation file
2. When you open the workbook you will notice in row 1 beneath columns L & M a command button labeled "Append"
3. What this does is add new tabs to the existing Cost Allocation workbook. You will be ask a year so please enter the two digit number 15
4. This workbook should now have twelve new tabs labeled Jan 15...Dec 15.
5. This works perfectly.
6. However, the consolidated worksheet only shows the data for the first year, in this case 2014 in Column B.
7. (Please disregard formulas for now. I'll get to that later. I'm trying to take this one small step at a time)
8. What the client is asking for is to automate adding the yearly summary ranges (row 6-17) using a macro (second question). This question is asking to move the rows 18 and 19 to 32 and 33 (if I counted correctly).
9. If they added a third year rows 32 and 33 would move to 44 and 45, etc. for year added year.
10. Row 18 is simply a spacer where the row is smaller and colored gray
11. Row 19 represents the total for the project. (Again, please disregard formulas and copying the details  to rows 20 and 31 at this point. More questions to follow)
I know I will be asking a lot from EE on this project. Contributions are so appreciated and if you need clarity no problem.
Project-Cost-Allocation-Revised.xlsm
Append.xlsm
Frank FreeseAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ejgil HedegaardCommented:
If the formula in row 19 Totals, is the sum of the above, and the sum includes row 18 (the blank), 12 new rows can be inserted, shifting row 18 and 19 down, automatically adjusting the formula.
In this macro the position of the text "Totals" in column A, determine where to insert the rows.
The last part of the macro copies the formats from A6:U17 to make it look like above.

Option Explicit

Sub InsertNewYearConsolidated()
    Dim ws As Worksheet, rw As Long
    Set ws = Worksheets("Consolidated")
    ws.Select
    
    'Insert 12 rows above Totals and the spacer row
    rw = ws.Range("A:A").Find("Totals").Row
    ws.Rows(((rw - 1) & ":" & (rw + 10))).Insert Shift:=xlDown
    
    'copy the format from the first 12 rows (6 to 17)
    ws.Range("A6:U17").Copy
    ws.Range("A" & (rw - 1)).Select
    Selection.PasteSpecial Paste:=xlPasteFormats
    Application.CutCopyMode = False
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Frank FreeseAuthor Commented:
Well I'll be. I've spent the better portion of my day with 3 times the amount of code that you used and we got the same results. I'm impressed. I have two unmet needs but I'm opening new questions for those.
0
Frank FreeseAuthor Commented:
GREAT! job
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.