Consolidate Data

[ashani]

Hi,

I wonder if someone please could guide me. In my spreadsheet I have several worksheet, however I only want to consolidate data from Sheet, A, B, C & E into one master sheet. The columns are the same, all I want everytime I click on consolidate, the system should copy all the data from the said sheet into one and don't duplicate, so it may be first clear the master sheet then copy the whole lot. The row and column I want the data copy from each sheet is A4:I1000.

Please can someone help me with this.

Thank you

 

[vcoolio]

Hello Ashani,

Assuming that your source worksheets are named "A", "B", "C"...etc., the following code should work for you:-

Option Explicit
Sub Test()

        Dim ar As Variant, i As Long, wsM As Worksheet
        ar = Array("A", "B", "C", "E")
        Set wsM = Sheets("Master")
       
Application.ScreenUpdating = False
       
        Sheet1.UsedRange.Offset(1).Clear
       
        For i = 0 To UBound(ar)
              Sheets(ar(i)).Range("A4:I1000").Copy wsM.Range("A" & Rows.Count).End(3)(2)
        Next i

Application.ScreenUpdating = True

End Sub

I'm also assuming that your Master sheet has headings in row1 with the transferred data to start in row2.

I hope that this helps.

Cheerio,
vcoolio.