need macro

[FrankSit]

Hello, Anybody can help me on how to make a macro to modify the lay out of my worksheet as shown in Sheet1, to be like the one shown in "To be" sheet. This is the format of report from a database that can be
copied to clip board in excel. Each person has multifield downward, and each group has a blank row cell in excel. In "to be", I want the period total to the line of the name. So that I can use vlookup each person's donation for 2023 compared with 2024

Thank you for any help

Frank

 

[Fuji]

Thank you very much Fuji, it works for me. My question is if the number of row is more than 50000, I just change the a50000?.

Thank you,

Frank

Yes correct.
Or you could also reference whole column, if you like (bit slower though)

        x = Filter(.[transpose(if(a:a<>"",row(a:a)))], False, 0)
        y = Filter(.[transpose(if(f:f<>"",row(f:f)))], False, 0)

 

[Fuji]

Perhaps this would be faster...

Sub test()
    Sheets("to be").Columns("a:b").Clear
    With Sheets("sheet1")
        On Error Resume Next
        .Columns("a").SpecialCells(2).Copy Sheets("to be").[a1]
        .Columns("f").SpecialCells(2).Copy Sheets("to be").[b1]
        On Error GoTo 0
    End With
End Sub

 

[FrankSit]

Did you try the other simple macro suggestion? (Already deals with any number of rows & clearing out any previous data.)

Not yet Pet, but I will try it later.

Thank you,

Frank