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

Merge sheets VBA

Hi,

I wish to merge sheet data (from multiple worksheets) into one consolidated worksheet (master sheet named POSITIVE) using VBA that runs before saving.

I would like to be able to loop through all of the worksheets except my master sheet and a worksheet named QFT. Ideally I would like to consolidate the data from each column (A to H) but only if the data in column G is "Yes". If this is not possible I'd be happy for all the data from each sheet to be consolidated into the master and then I can filter it.

The workbook will be added to continually over time so I would like a solution that will not create duplicate data in the master sheet.

I have found some consolidate VBAs on the web but I'm a novice with VBA so I haven't managed to amend one to meet my need.
I hope someone can help, thanks in advance.
 
Sam Longstaff
Your The workbook will be added to continually over time so I would like a solution that will not create duplicate data in the master sheet.
What is duplicate for You? ... which columns?
eg if only E-column value is different or H-column value has filled ... should code overwrite older line or add new line?
... Your sample file seems to have ... two lines data ... hmm?
 
Sam Longstaff
Your The workbook will be added to continually over time so I would like a solution that will not create duplicate data in the master sheet.
What is duplicate for You? ... which columns?
eg if only E-column value is different or H-column value has filled ... should code overwrite older line or add new line?
... Your sample file seems to have ... two lines data ... hmm?


Hi,

The example only has two lines of data. If you can imagine that each sheet has 10+ lines of data from which 1 or 2 lines are taken to the master file. If the macro runs again at a later date I wouldn't want those same two lines of data to be added in again. Perhaps the solution would be for the master sheet to be deleted and then filled again from the macro looping through the sheets?
 
According to your attachment a VBA beginner starter to paste to the POSITIVE worksheet module :​
Code:
Private Sub Worksheet_Activate()
        Application.ScreenUpdating = False
        [K1:K2].Value2 = Evaluate("{""" & [G1].Text & """;""Yes""}")
    For N& = Index + 2 To Sheets.Count
        With Sheets(N).[A1].CurrentRegion
            If IsNumeric(Application.Match("Yes", .Columns(7), 0)) Then
                If IsEmpty([A2]) Then
                   .AdvancedFilter 2, [K1:K2], [A1:H1]
                Else
                   .AdvancedFilter 1, [K1:K2]
                   .Offset(1).Copy Cells(UsedRange.Rows.Count + 1, 1)
                    If .Parent.FilterMode Then .Parent.ShowAllData
                End If
            End If
        End With
    Next
        Application.ScreenUpdating = True
End Sub

Private Sub Worksheet_Deactivate()
    UsedRange.Offset(1).Clear
End Sub
Do you like it ? So thanks to click on bottom right Like !​
 
Back
Top