Help with code to consolidate data from specific sheets to one sheet.

[Fred Wayne]

Hello, my dear friends.
I have a little issue with my new workbook It has a total of 8 worksheets( Those ones are 1, 2, 3, 4, 5, 6, Projects, and Top 10). There is another sheet called "Master Sheet".
I need to find a way to copy all the data that is in worksheets 1,2, 3, 4, 5, and 6, not including the data of sheets "Projects" and "Top 10" and then paste it the worksheet called "Master Sheet"?

Is there a code for me to copy that information in just a simple click?


I am attaching the file I am currently using.
Thank you so much in advance.

 

[pecoflyer]

Perhaps https://www.rondebruin.nl/win/addins/rdbmerge.htm
or

Copy/Paste/Merge examples

 

[mohadin]

Hi Fred
Try this code

 

[Fred Wayne]

Thank you so much!!! It worked perfectly!.
But now my situation is the following:
I used the same code you gave me to use it in another Workbook. This new workbook has 11 working sheets called "1st, 2nd, 3rd, 4th, 5th, 6th, 7th, 8th, 9th, 10th, and 11th". For some reason, I can´t fathom; the information is copied, but instead of being copied from cells "A7" of each sheet, it is copied but from "A2". Plus...the rest of the information is pasted but not in order...so you have to scroll down to find that information. How can I fix that? I am uploading some images of the errors and the original file. Once again, thank you so much for your amazing assistance.

P.S: This is the code that you gave me but I made a little modification to fit my workbook.

Sub consolidate()
    Dim c As New Collection
    Dim a, b As Variant
    Dim arr
    Dim i, l
    ReDim d(1 To 11)
    arr = Array("1st", "2nd", "3rd", "4th", "5th", "6th", "7th", "8th", "9th", "10th", "11th")
    For i = 1 To UBound(arr) + 1
        With Sheets(arr(i - 1))
            a = .Cells(2, 1).CurrentRegion.Offset(1).Value
            c.Add a
            d(i) = Array(c)
        End With
    Next
        l = 1
    For i = 1 To UBound(d)
        Sheets("Master Sheet").Range("a" & l + 1, "j" & l + UBound(d(i)(0)(i)) - 1) = d(i)(0)(i)
        l = l + UBound(d(i)(0)(i)) - 1
    Next
End Sub

 

[mohadin]

Hi
Try This and let me Know

 

[Fred Wayne]

THAT IS EXACTLY WHAT I NEEDED IT!
Wow!!!! You are simply awesome! Thank you so much...You are definitely the best, You all on this Forum are the best. Thank you so much for your support.
2THUMBS UP AND KUDOS FOR YOU MOHADIN!!!!! THANK YOU!

 

[mohadin]

Ohhh
Thank you so much
I'm so glad I could help you
Thank you
Be Happy

 

[Fred Wayne]

I have one more request(THE LAST ONE, I PROMISE). I have created a simple userform. I want all data from "Master Sheet" to be displayed in the ListBox. If I click on any value from that ListBox, I would like all data to fill the corresponding TextBoxes on top. The same way with the "TYPE TO SEARCH" TextBox====> I would like that when I type any value on that TextBox, it filters and display the corresponding match. Of course, after filtering and clicking on any searched value, the TextBoxes on top to display the corresponding information.

I am attaching images and the original file with the Userform.


Thank you so much Mohadin in advance.
You are magnificently the best!

 

[mohadin]

irrelevant issue

 

[Fred Wayne]

I don't understand what you mean with irrelevant issue my dear friend! But I want to thank you for all your support. I'll try to figure it out how to solve it. God bless.

P.S: You're the best.