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 $.

Column H		Column N
Total Gross		123.00
Total Deductions		456
Total Tax Including Extra Tax		789.00
Total Employee's Super		1011
Total Net Pay		1,213.00
Total Employer's Super		1,314.00
Total Salary Sacrifice		1,415.00
Total Fringe Benefits		0

<colgroup><col><col><col></colgroup><tbody>
</tbody>

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")