Move rows macro

Posted on 2014-07-16
Last Modified: 2014-07-16
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.
Question by:Frank Freese
    LVL 20

    Accepted Solution

    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")
        '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("A" & (rw - 1)).Select
        Selection.PasteSpecial Paste:=xlPasteFormats
        Application.CutCopyMode = False
    End Sub

    Open in new window


    Author Comment

    by:Frank Freese
    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.

    Author Closing Comment

    by:Frank Freese
    GREAT! job

    Featured Post

    Courses: Start Training Online With Pros, Today

    Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Search multiple lines 3 27
    Protecting an object 3 26
    Tricky lookup and concatenate formula 6 30
    Status Change 3 7
    Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
    This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
    The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
    This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now