cmschmitz24
Board Regular
- Joined
- Jan 27, 2017
- Messages
- 150
Hello,
I have a macro that needs some tweaking and help.
Currently it take data from the first tab, breaks it out into new tabs based on data in column A. Then within the new tabs, it formats each sheet by adding "total" rows after a new value in column B. The new row adds an auto sum to columns J and L and a grand total at the bottom using the sums from column L.
I need to have column I do the same thing as column B in the new row created, but then remove the contents in the cells above the new row. So the information is only in the new row created in column I.
Please see before/after pictures.
Before:
After:
I have also included my code below -
Thanks, I appreciate any help!
I have a macro that needs some tweaking and help.
Currently it take data from the first tab, breaks it out into new tabs based on data in column A. Then within the new tabs, it formats each sheet by adding "total" rows after a new value in column B. The new row adds an auto sum to columns J and L and a grand total at the bottom using the sums from column L.
I need to have column I do the same thing as column B in the new row created, but then remove the contents in the cells above the new row. So the information is only in the new row created in column I.
Please see before/after pictures.
Before:
After:
I have also included my code below -
Code:
'adds grey total row after invoice number and grand total
For i = Range("B" & Rows.Count).End(xlUp).Row + 1 To 3 Step -1
If Range("B" & i).Value <> Range("B" & i - 1).Value Then
Range("B" & i).EntireRow.Insert
Range("B" & i).Offset(0, -1).Resize(, 12).Interior.Color = 14277081
Range("B" & i).Offset(, 1).Value = "Total"
Range("B" & i).Offset(, 1).Font.Bold = True
End If
Next i
TotRw = Range("L" & Rows.Count).End(xlUp).Offset(3).Row
For Each Rng In Range("L2:L" & TotRw - 1).SpecialCells(xlConstants).Areas
With Rng.Offset(Rng.Count, -10).Resize(1)
.Value = Rng.Offset(, -10).Value
.Font.Bold = True
End With
Rng.Offset(Rng.Count).Resize(1).Formula = "=sum(" & Rng.Address & ")"
Rng.Offset(Rng.Count).Resize(1).Font.Bold = True
Next Rng
With Range("L" & TotRw)
.Formula = "=sum(" & Range("H2:H" & TotRw - 2).SpecialCells(xlBlanks).Offset(, 4).Address & ")"
.Offset(, -1).Value = "Grand Total"
.Offset(, -1).Resize(, 2).Font.Bold = True
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlDouble
End With
'adds sub totals in column J
For Each Rng In Range("J2:J" & TotRw - 1).SpecialCells(xlConstants).Areas
With Rng.Offset(Rng.Count, -8).Resize(1)
.Value = Rng.Offset(, -8).Value
.Font.Bold = True
End With
Rng.Offset(Rng.Count).Resize(1).Formula = "=sum(" & Rng.Address & ")"
Rng.Offset(Rng.Count).Resize(1).Font.Bold = True
Next Rng
Thanks, I appreciate any help!