Copy data in column based on cell value

[oakman]

Hello Tom,

Thank you very much for your help. I shall try to answer you
questions as best I can. The word "Total" appears as a result of
formula. It will appear in any column in row 4 on the sheet dependin
on the data. The data will change from day to day so the word "Total
will appear in a different column each day but always in row 4. (doe
this make sense?) I then would like to copy just the selected chunck o
rows 6-30 of the column where the word "Total" appears.

I appreciate your help very much. Looking at the piece of code offere
to me, it all makes sense. It is just that initial step that totall
freezes me

 

[Tom Ogilvy]

See if this does what you want.

Sub CopyDateFromTotalColumn()
Dim cell as Range
Dim cell1 as Range
Dim rng as Range
With Worksheets("INCS&DECS")
for each cell in .Rows(4).Cells
if instr(1,cell.Value,"Total",vbTextCompare) then
set cell1 = cell
exit for
end if
Next
set rng = Intersect(cell1.EntireColumn, .rows("6:30"))
End With
rng.copy Destination:=Worksheets("Input").Range("B9")
End With

You didn't say where on sheet INPUT to place the data, so replace the B9
with the top most cell where you want it copied to.

if you want to just paste values you can do
rng.copy
Worksheets("Input").Range("B9").PasteSpecial xlValues