Creating a new blank row and moving data to that row

[BigEInMT]

I am currently configuring a financial report which lists description data as well as financial data. I will post a copy of the layout below. What I'm looking to do, is to use a macro/vba or whatever I need to in order to allow me to create a blank row above any row which contains the word Totals. I'd then like to move the data from Column 15 and paste the data into Column A of the new blank row. I'd also like to Merge the cells from the first column through Column 8 for the new blank row but that may be asking too much. I'm going to post what the report looks like now and what ultimately I would like the report to look like after the changes are made.

Now:

	Column 1	Column 2	Column 3	Column 4	Column 5	Column 6	Column 7	Column 8	Column 9	Column 10	Column 11	Column 12	Column 13	Column 14	Column 15
Number: 1
	Data	Data	Data	Data	Data	Data	Data	Data	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	Description
	Data	Data	Data	Data	Data	Data	Data	Data	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00
Totals	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00
Number: 2
	Data	Data	Data	Data	Data	Data	Data	Data	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	Description
	Data	Data	Data	Data	Data	Data	Data	Data	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00
	Data	Data	Data	Data	Data	Data	Data	Data	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00
	Data	Data	Data	Data	Data	Data	Data	Data	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00
Totals	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00

<tbody>
</tbody>

What I'd like it to look like:

	Column 1	Column 2	Column 3	Column 4	Column 5	Column 6	Column 7	Column 8	Column 9	Column 10	Column 11	Column 12	Column 13	Column 14	Column 15
Number: 1
	Data	Data	Data	Data	Data	Data	Data	Data	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00
	Data	Data	Data	Data	Data	Data	Data	Data	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00
Description
Totals	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00
Number: 2
	Data	Data	Data	Data	Data	Data	Data	Data	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00
	Data	Data	Data	Data	Data	Data	Data	Data	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00
	Data	Data	Data	Data	Data	Data	Data	Data	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00
	Data	Data	Data	Data	Data	Data	Data	Data	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00
Description
Totals	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00

<tbody>
</tbody>

 

[kweaver]

Re: Help creating a new blank row and moving data to that row

In my previous macro, change the IF line to read:

If Left(Range("A" & i), 6) = "Totals" Then

 

[BigEInMT]

Re: Help creating a new blank row and moving data to that row

The macro was able to create the new blank row above all rows with "Totals" in them. Thank you so much for that as it will save a bunch of time. Unfortunately, the description didn't copy over to Row A in the new blank row. The description stayed where it was in Row P.

 

[BigEInMT]

Re: Help creating a new blank row and moving data to that row

I know I'm asking a lot here but is there also a way to add "Loss Description:" in front of the "Description" data which is moved from Column P to Column A of the newly created blank row? The end result would create the blank row above Totals, which you've already helped me with. Add "Loss Description:" to Column A of the new blank row and then move the "Description" data from Column P to Column A of the new blank row so it looks like "Loss Description: Description".

 

[kweaver]

Re: Help creating a new blank row and moving data to that row

That's odd as it copies over when I run it.

RE: your other question, change the assignment line to:

Cells(i, 1) = "Loss Description: " & Cells(n + 1, 16).Value

 

[BigEInMT]

Re: Help creating a new blank row and moving data to that row

We are so close! The code I've used, which you've provided, will be below. Right now, the Description in Column P is still staying in Column P and not moving to the blank row in Column A. After adding your latest code, Loss Description gets added to the newly created blank row. I want to make sure I've entered the code correctly and I'm not missing something simple which is causing the descriptions in Column P not to move to Column A.

Sub InsertAdj()
Dim LR As Long, i As Integer, n As Integer
LR = Cells(Rows.Count, "A").End(xlUp).Row
For i = LR To 2 Step -1
If Left(Range("A" & i), 6) = "Totals" Then
Rows(i).Insert Shift:=xlDown
n = WorksheetFunction.Match(WorksheetFunction.Lookup("zzzzz", Range("A1:A" & i)), Range("A1:A" & i), 0)
Cells(i, 1) = "Loss Description: " & Cells(n + 1, 16).Value
Cells(n + 1, 16).ClearContents
End If
Next i
End Sub