Solved

# Move rows macro

Posted on 2014-07-16
158 Views
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
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")
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
``````
0

Author Comment

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

Author Closing Comment

GREAT! job
0

## Featured Post

### Suggested Solutions

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.