Help - Find value, copy range and move cells to different columns

[mompy76]

Hi

I have a spreadsheet with approx 1000 lines of data which needs to be formatted. The lines number varies each week. I need to amend my macro so that it can capture/move the necessary data to the correct columns regardless of the number of rows. Column H contains subtotal headings (ie Total Gross) and column N contains the total $.

[TABLE="width: 319"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Column H[/TD]
[TD][/TD]
[TD]Column N[/TD]
[/TR]
[TR]
[TD]Total Gross[/TD]
[TD][/TD]
[TD="align: right"]123.00[/TD]
[/TR]
[TR]
[TD]Total Deductions[/TD]
[TD][/TD]
[TD="align: right"]456[/TD]
[/TR]
[TR]
[TD]Total Tax Including Extra Tax[/TD]
[TD][/TD]
[TD="align: right"]789.00
[/TD]
[/TR]
[TR]
[TD]Total Employee's Super[/TD]
[TD][/TD]
[TD="align: right"]1011[/TD]
[/TR]
[TR]
[TD]Total Net Pay[/TD]
[TD][/TD]
[TD="align: right"]1,213.00[/TD]
[/TR]
[TR]
[TD]Total Employer's Super[/TD]
[TD][/TD]
[TD="align: right"]1,314.00[/TD]
[/TR]
[TR]
[TD]Total Salary Sacrifice[/TD]
[TD][/TD]
[TD="align: right"]1,415.00[/TD]
[/TR]
[TR]
[TD]Total Fringe Benefits[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]
​

I need the macro to be able to find the word "Total" in Column H, select the data Hxx:Nxx (ie 8 rows of total data), move Column H contents to Column G, and move Column N contents to Column I.

I would greatly appreciate all assistance with this.

Cheers
Mompy76

 

[davesexcel]

Hi,
You have indicted you want to amend your macro, but you haven't supplied the code.

 

[mompy76]

Hi,
You have indicted you want to amend your macro, but you haven't supplied the code.

The code I have doesn't work at as it should, and deletes required content, as the cell row range I used changes each week. This was only noticed in the week following the creation of this macro when totals were out of balance on the linked spreadsheets.

This is what I have now:-

Selection.Find(What:="total", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Range("H637:H700").Select
Selection.Cut Destination:=Range("G637:G700")
Range("N637:N700").Select
Selection.Cut Destination:=Range("I637:I700")