Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 164
  • Last Modified:

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
0
Frank Freese
Asked:
Frank Freese
  • 2
1 Solution
 
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
 
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

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now