Copy and paste dynamic ranges from mulitple sheets to summary sheet

[waqasbutt]

Hi All,

I am new to VBA and I am struggling with a scenario. I have a sheet "Rawdata" and I using advance filter to extract unique record based on certain criteria into five different sheets.
In Summary sheet (attached image, how I want to display), I want to report all five tabs data into five tables in summary sheet. Since data is dynamic, so I am facing challenge to adjust rows for each table in summary sheet. Sometimes it overlap to second table or have lot of empty rows.

Any help here would appreciated.

Regards,
Waqas

 

[sandy666]

use Power Query. No problem with overlapping

 

[mohadin]

Hi
How about

Sub test()
    Dim a As Variant
    Dim i, k As Long
    Sheets("Result").Cells.ClearContents
    ReDim a(1 To Sheets.Count - 1)
    For i = 1 To Sheets.Count
        If Sheets(i).Name <> "result" Then
            a(i) = Sheets(i).Range("a1").CurrentRegion
        End If
    Next
    k = 1
    For i = 1 To UBound(a)
        Cells(k, 1).Resize(UBound(a(i)), UBound(a(i), 2)) = a(i)
        k = UBound(a(i)) + k + 2
    Next
End Sub

distination sheets name "Result"

 

[mohadin]

Ver.2

Sub test()
    Dim a As Variant
    Dim i, k, LR As Long
    k = 1
    Sheets("result").Cells.ClearContents
    For i = 1 To Sheets.Count
        If Sheets(i).Name <> "result" Then
        LR = Sheets(i).Cells(Rows.Count, 1).End(xlUp).Row
            a = Sheets(i).Range("a1").CurrentRegion
            Sheets("result").Cells(k, 1).Resize(UBound(a), UBound(a, 2)) = a
            k = LR + k + 2
        End If
    Next
End Sub

 

[waqasbutt]

Dear All,

I used Record Marco function to generate this code but I do not know how I can handle summary tab.
I cannot upload my sample file so I am pasting images of different tabs I have.

Rawdata is data I am getting and then Marco based on Criteria tab using advance filter populate Status=Started and Status=Closed tab.
1. I am not able to collect data from both Status tab into Summary tab and dynamically adjust each status table in Summary tab.
2. Plus, I have link column in Rawdata but when I using advance filter, it copy text and formatting but not the link.

Is there any way perhaps to select data from Rawdata directly in Summary tab in different tables dynamic adjust the rows for each table.

Sub Sortdata_v1() ' ' Sortdata_v1 Macro ' ' Range("D5").Select ' As I do know number of rows in Rawdata so I put E column 100 which I think will be max. Ideally, I would like this to adjust as well to last row has data Sheets("Rawdata").Range("A1:E100").AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=Sheets("Criteria").Range("A1:B3"), CopyToRange:=Range( _ "'Status=done'!Extract"), Unique:=True Sheets("Status=Closed").Select Range("E5").Select Sheets("Rawdata").Range("A1:E100").AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=Sheets("Criteria").Range("E1:E2"), CopyToRange:=Range( _ "A1:E1"), Unique:=True Sheets("Summary").Select Range("G23").Select Sheets("Rawdata").Range("A1:E100").AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=Sheets("Criteria").Range("H1:H2"), CopyToRange:=Range( _ "C19:G19"), Unique:=True End Sub

Regards,
Waqas

 

[waqasbutt]

use Power Query. No problem with overlapping

Thanks, I will try that as I never used Power Query as well.

 

[waqasbutt]

Ver.2

Sub test()
    Dim a As Variant
    Dim i, k, LR As Long
    k = 1
    Sheets("result").Cells.ClearContents
    For i = 1 To Sheets.Count
        If Sheets(i).Name <> "result" Then
        LR = Sheets(i).Cells(Rows.Count, 1).End(xlUp).Row
            a = Sheets(i).Range("a1").CurrentRegion
            Sheets("result").Cells(k, 1).Resize(UBound(a), UBound(a, 2)) = a
            k = LR + k + 2
        End If
    Next
End Sub

Thanks for the code. I just finished the same activity through Power Query and it works actually I needed.

 

[waqasbutt]

use Power Query. No problem with overlapping

Thanks for suggestion, I worked, perfectly! Appreciated

 

[sandy666]

I just finished the same activity through Power Query and it works actually I needed.

good to see that
Have a nice day