1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

  3. 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

Discussion in 'VBA Macros' started by rxk, Jan 1, 2019.

  1. rxk

    rxk New Member

    Messages:
    10
    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.

    Attached Files:

  2. rxk

    rxk New Member

    Messages:
    10
    Anyone can help on this.
  3. vletm

    vletm Excel Ninja

    Messages:
    4,918
    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.
  4. rxk

    rxk New Member

    Messages:
    10
    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.

    Attached Files:

  5. vletm

    vletm Excel Ninja

    Messages:
    4,918
    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!
  6. charlesdh

    charlesdh Member

    Messages:
    79
    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 (vb):

    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
    rxk likes this.
  7. rxk

    rxk New Member

    Messages:
    10
    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.



  8. charlesdh

    charlesdh Member

    Messages:
    79
    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!
  9. charlesdh

    charlesdh Member

    Messages:
    79
    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!
  10. rxk

    rxk New Member

    Messages:
    10
    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.
  11. charlesdh

    charlesdh Member

    Messages:
    79
    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" .
    rxk likes this.
  12. rxk

    rxk New Member

    Messages:
    10
    Thanks. That's what I am doing now.
  13. charlesdh

    charlesdh Member

    Messages:
    79
    rxk,
    Thanks for the "Like".

Share This Page