i am looking for solution of my problem, i have a huge data in table, here is only kind of example.

What i want to get as result step by step is :

1. Filter table on Column D, select values > 0
2. Copy to new Sheet (name : NEW) data from A~C , then to column D paste values, to column E paste date 01-02
3. Filter table on Column E, select values > 0
4. Copy to new Sheet (name : NEW) data from A~C - paste now under last data , then to column D paste values, to column E paste date 01-03

that loop should be done until last column with value (here column H)

Final expectation :

I was trying to make some VBA by recorder but unfortunatelly my table size is changing and without VBA code it just not working. Hope someone might have an idea for that. Cheers

CodePudding user response：

i've tried

`

`Sub CopyData() Dim lastRow As Long Dim i As Long

'Find the last row with a value in column D
lastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "D").End(xlUp).Row

'Loop through column D starting at row 1
For i = 1 To lastRow
    If ActiveSheet.Cells(i, "D").Value > 0 Then
        'Copy the range from column A to D in that row
        ActiveSheet.Range("A" & i & ":D" & i).Copy
        'Paste the copied range to the NEW sheet
        Sheets("NEW").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
    End If
Next i

End Sub

`

CodePudding user response：

It looks like this does not need any VBA at all; it can easily be solved in a query.

1. Select your table
2. Go to menu Data > Get Data > From other sources > From Table / Range
3. Click OK on the small window that opens.
4. In the PowerQuery editor, simply unpivot the date columns
5. There are small filter / sorting adjustment to make but it should be trivial.
6. Click Close & Load

Alternatively to steps 4 and 5, in the PowerQuery editor, you can open open the advanced editor and paste this (note my Excel named the range Table1):

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {"Sort", "Object", "Dest"}, "Date", "Qty"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Columns", each [Qty] <> 0),
    #"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Date", Order.Ascending}, {"Object", Order.Ascending}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Sorted Rows",{"Sort", "Object", "Dest", "Qty", "Date"})
in
    #"Reordered Columns"

If you want really want some VBA (e.g. auto-refresh in a Worksheet_Activate event), you can record yourself refreshing the table.