Hi guys, I am pretty new to Macro / VBA and I could not find the answer for the captioned after I google. Was wondering if anyone could help here...

I would like Excel to select all rows which has a cell with 'Done' in DONE CASE Worksheet. Then I want it paste under the Done Case header in another worksheet called 'MASTER'. This header is located on row 23 in the MASTER worksheet.

Excel successfully paste the data under the header (row 23) for the first time. But after I move the header to row 26, it fails to paste the data correctly. The data still goes under row 23 when the header is on row 26 now.

This header varies every time I produce a new report. How could I write a code or record a Macro so Excel knows the data must be pasted under the header?

Please refer to the below code I used for your easy reference.


    Sheets("DONE CASE").Select
Dim rngA As Range
Dim cell As Range
Set rngA = Range("T1", Range("T65536"))
For Each cell In rngA
If cell.Value = "Done" Then
cell.EntireRow.Copy
    Sheets("MASTER").Select
    Rows("23:23").Select
    Selection.Insert Shift:=xlUp
    End If


Next


End Sub
The header for all sections are actually the same. The only difference lines on the topic name above the header. Possible to identify the row 23 header (which may not be on row 23 in next report) in this case?

The report is sectioned as below -

Row 1: Topic - Pending case
Row 2: Header

Row 22: Topic - Done case
Row 23: Header

Great thanks for your kind help as I am really new to VBA and Macro!