Merging worksheets of same name into single worksheet

Hi All,

I'm having some issues with how manual a process this is for me. Hoping for some guidence here in the forum!

I have a huge workbook with 7 tabs. Each tab has customer information on it.

Each month I have to filter for each customer across all 7 tabs and paste that customer's information into a new workbook. The end result is a new workbook with 7 tabs for each customer. Is there any way I can employ VBA to make this process quicker/less manual?

Any direction is very much appreciated! Thank you!

I should add my title is wrong. I'm not sure how to edit it. It should say " Merging worksheets of same name into single workbook"

Hello again, Xladept! So glad that you in particular responded to my post!

I've attached my master file. I want to break it into new files, with each file having 3 tabs. New file 1 should have tabs 1, 1(1), and 1(2). The new File 2 should have tabs 2, 2(1), and 2(2), and so on.

I tried to peice together what little I know about code/could find on the interweb. I am able to successfuly copy the sheets I need and rename the tabs appropriately. The problem I have with the code below is that it's only for the first file, and I'm in need of something that will account for the fact that the number of files that I need to create may change. One month I may have tabs that go to 10, 10(1), 10(2) and one month I may have tabs that go to 25, 25(1), 25(2). Also, the code below doesn't automatically save the workbook. I've attached the before and after.


Sub test()
Sheets(Array("1", "1 (1)", “1(2)”).Select
Sheets(Array("1", "1 (1)", “1(2)”).Copy
For x = 1 To Sheets.Count
If Worksheets(x).Range("A1").Value <> "" Then
Sheets(x).Name = Worksheets(x).Range("A1").Value
End If
Next
End Sub