• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need help : VBA to consolidate data range from multiple worksheets

rxk

New Member
Hi all,

I would like to consolidate the following data ranges using VBA.
1. copy range H4:J(last row) of all sheets from Jan-19 to the last sheets. Number of worksheets may be more than 12. All datas copied will be stored on sheet(P-List) and remove duplicate based on SKU ID column. (see sample P-List-result)
2. copy range K3:K(last column) of all sheets from Jan-19 to the last sheets. All datas copied will stored on sheet(A-List), transpose and then remove duplicate.

Is it possible the sheets to be copied from are not name specific?

Thank you in advance.
 

Attachments

vletm

Excel Ninja
rxk
This seems to be 2nd part of SelectedSum's ...
Without even sample values in those sheets it would be a challenge to verify results.
eg which duplicate would remove, older or newer data?

Sheet names should have clear rules
if You would like to get results to same file.
 

rxk

New Member
Hi vletm,

Yes, it is another part of the of the SelectedSum workbook.

I am not well versed with VBA so I try to break into manageable parts for me to understand VBA.

Updated version
  • I added prefix (data_) to all worksheet need copy values from
  • with my limited vba coding skills, i've created a recorded macro to in order to describe on how get the desired results (worksheets P-List-desired result & A-List-desired result)
  • recorded macros where assigned on the buttons located at worksheets P-List and A-List
Thank you for your patience.
 

Attachments

vletm

Excel Ninja
Okay - step-by-step ...
For other than You, it better to know all steps,
because even then there should only steps forward!

I tried to give hints... and ...
> Still empty sheets!
Is empty range copies to other place ... how to verify?
> older or newer duplicates away?
>> You need those answers, not me!
 

charlesdh

Member
Hi,
Here's a macro that may be able to use. Copy and past to a new Module.
You may to modify it for a "Header" in the A_List worksheet.


Code:
Sub Consolidate_Data()
Application.ScreenUpdating = False
Application.Calculation = xlManual
Dim ws As Worksheet
Dim lrow As Long
Dim Plrow As Long
Dim Alrow As Long
    For Each ws In Worksheets
        If ws.Name <> "P-List" And ws.Name <> "A-List" Then
            lrow = ws.Range("H" & Rows.Count).End(xlUp).Row '' last row for monthly worksheet
            Plrow = Sheets("P-List").Range("A" & Rows.Count).End(xlUp).Row
            Alrow = Sheets("A-List").Range("A" & Rows.Count).End(xlUp).Row
            ws.Range("H13:J" & lrow).Copy Destination:=Sheets("P-List").Range("A" & Plrow + 1)
            ws.Activate
            Range("K3", [K3].End(xlToRight)).Copy
            Sheets("A-List").Range("A" & Alrow + 1).PasteSpecial Transpose:=True
            Application.CutCopyMode = False
    End If
        Sheets("P-List").Activate
    Next ws
    ''' Remove duplicates in "A_List"
    Sheets("A-List").Cells.RemoveDuplicates Columns:=Array(1)
    Application.ScreenUpdating = True
    Application.Calculation = xlAutomatic
End Sub
Charles
 
  • Like
Reactions: rxk

rxk

New Member
Hi charlesdh,

Your code is working for the my desired result but i like to change the code to copy all worksheets (starts with "data")
If ws.Name <> "P-List" And ws.Name <> "A-List" Then

Thank you.



 

charlesdh

Member
rxk,
The code that I posted work for you file that you wanted to look at "data".
The code loops thru all work sheets that are not "P-List" and "A-List".
So I'm not sure what!
 

charlesdh

Member
HI,
Had to correct my previous.

The code loops thru all work sheets that are not "P-List" and "A-List".
It does not matter what the sheet name are as long as it's not "P-List" and "L-List"
So I'm not sure what you mean!
 

rxk

New Member
Hi Charlesdh,

The reason that I ask is that aside from P-List & A-List, their are other sheets that I want to exclude.

Thank you.
 

charlesdh

Member
Hi,
All you need to do is add the excluded sheet.
If ws.name <> "A-List" and ws.name <> "P-List" and ws.name <> "Data-List" .
 
  • Like
Reactions: rxk
Top