Excel Ninjas: Loop Copy/Paste Category Types at Row Level

[Dirock]

Hello,

I am trying to create a macro that does the following:

1. Loop down sheet and copy the Description (Column E) Ex: Copy "Cash in Bank" [Note: the empty cells at the category description level (Beg and End)]
2. Paste the description in (Column L) next to each transaction Ex: Paste "Cash in Bank" in cells L3 and L4
3. Continue loop until end of sheet, which can be 1000+ transactions and with multiple categories

[TABLE="class: grid, width: 600"]
<tbody>[TR]
[TD]Property[/TD]
[TD]Property Name[/TD]
[TD]Date[/TD]
[TD]Period[/TD]
[TD]Description[/TD]
[TD]Control[/TD]
[TD]Reference[/TD]
[TD]Debit[/TD]
[TD]Credit[/TD]
[TD]Balance[/TD]
[TD]Remarks[/TD]
[TD]Category[/TD]
[/TR]
[TR]
[TD]1120-2450[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Cash in Bank[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]100[/TD]
[TD]= Beginning Balance =[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]101st[/TD]
[TD]101 Street[/TD]
[TD]3/1/2019[/TD]
[TD]Mar 2019[/TD]
[TD]Bank of Excel[/TD]
[TD]56465[/TD]
[TD]564654[/TD]
[TD]0[/TD]
[TD]5[/TD]
[TD]105[/TD]
[TD]Loan#123[/TD]
[TD](Cell L3)[/TD]
[/TR]
[TR]
[TD]wall4[/TD]
[TD]123 Wall St[/TD]
[TD]3/19/2019[/TD]
[TD]Mar 2019[/TD]
[TD]Bank of Excel[/TD]
[TD]8794[/TD]
[TD]13812[/TD]
[TD]0[/TD]
[TD]10[/TD]
[TD]115[/TD]
[TD]Inv# 456[/TD]
[TD](Cell L4)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Net Change = 20[/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[TD]15[/TD]
[TD]115[/TD]
[TD]= Ending Balance =[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1115-0000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Utility Deposits[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]20.00[/TD]
[TD]= Beginning Balance =[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]104th[/TD]
[TD]104th street[/TD]
[TD]3/22/2019[/TD]
[TD]mar 2019[/TD]
[TD]The Gas Company[/TD]
[TD]576841[/TD]
[TD]135451[/TD]
[TD]0[/TD]
[TD]5[/TD]
[TD]25[/TD]
[TD]CheckScan #2001[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]50ths[/TD]
[TD]50th street[/TD]
[TD]3/8/2019[/TD]
[TD]mar 2019[/TD]
[TD]The Gas Company[/TD]
[TD]484132[/TD]
[TD]1568412[/TD]
[TD]0[/TD]
[TD]5[/TD]
[TD]30[/TD]
[TD]454654[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Net Change = 5[/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[TD]10[/TD]
[TD]30[/TD]
[TD]= Ending Balance =[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Each category has a different amount of transactions each month. Any help would be great with this code. Thank you in advance!

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub Dirock()
   Dim Rng As Range
   For Each Rng In Range("F2", Range("F" & Rows.Count).End(xlUp)).SpecialCells(xlConstants).Areas
      Rng.Offset(, 6).Value = Rng.Offset(-1, -1).Resize(1, 1).Value
   Next Rng
End Sub

 

[Dirock]

Hi & welcome to MrExcel.
How about

Sub Dirock()
   Dim Rng As Range
   For Each Rng In Range("F2", Range("F" & Rows.Count).End(xlUp)).SpecialCells(xlConstants).Areas
      Rng.Offset(, 6).Value = Rng.Offset(-1, -1).Resize(1, 1).Value
   Next Rng
End Sub

Thank you so much! This code is beautiful and so much simpler than what I was trying to put together!

 

[Fluff]

You're welcome & thanks for the feedback