VBA Loop to go through all sheets. Then Copy a range and Paste it to Main sheet

[isanka88]

Hi Good People.

I have an excel workbook with 5 different work sheets. First work sheet is the Master sheet and others are customer data sheets . I want a VBA loop to go through all 4 customer data sheets >> then copy a specific range in each individual sheets >> paste it to master sheet.

However it does not simple as it sounds because data stored in very un structured way. I have attached my workbook below and I have mentioned my expected outcome on it. Kindly check and help me to find a work around for this. Thank you very much.

My workbook.xlsx

Master Sheet 03/01/2022 Customer1,Product Name,Price Gatorade,4.97 Oak,9.52 Joe's Classics,5.48,THE YELLOW HIGHLIGHTED RANGES ARE FIXED RANGES. EACH CUSTOMER HAS AN UNIQUE NUMBER OF ROWS Joe's Classics,6.48 Joe's Classics,7.48 Joe's Classics,8.48 Joe's Classics,9.48 This is the output I need 03...

docs.google.com

 

[mumps]

Where does the date come from?

 

[isanka88]

Where does the date come from?

Hey mumps, Thank you very much for coming back. Dates are fixed. Date cells are not changing. I manually enter dates. Master sheet format is fixed

 

[mumps]

The location (row number) of the dates will depend on the number of rows of data for each customer. If all the dates are entered manually, you would have to check the number of rows of data for each customer. The same is true for the size of the customer labels in column A. Are the headers "Product Name" and "Price" and "Customer" labels in column A also entered manually? Please clarify in detail.

 

[isanka88]

Hi Mumps, Let me share with you. The number of rows of data for each customer are fixed. For an example Customer1 has only 7 rows. Customer2 has only 3 rows. Those are not going to change. Also in the Master sheet, column A is manually entered. Dates, Product names and price heading are also manually entered and fixed locations. I just need those customer's selected fixed range to be copy and pasted in to the master sheet. I have further elaborated my expectation in the below sheet. Kindly have a look.

My workbook.xlsx

Master Sheet 03/01/2022 Customer1,Product Name,Price Gatorade,4.97 Oak,9.52 Joe's Classics,5.48,THE YELLOW HIGHLIGHTED RANGES ARE FIXED RANGES. EACH CUSTOMER HAS AN UNIQUE NUMBER OF ROWS Joe's Classics,6.48 Joe's Classics,7.48 Joe's Classics,8.48 Joe's Classics,9.48 This is the output I need 03...

docs.google.com

 

[mumps]

The dates on the Master Sheet are in merged cells. You should avoid merging cells because they almost always cause problems for macros. Do a little research into "CenterAcrossSelection". This has the same effect as merging without actually merging any cells. Please unmerge the cells in columns C and D containing the dates and then try this macro:

Sub CopyData()
    Application.ScreenUpdating = False
    Dim ws As Worksheet, desWS As Worksheet, fnd As Range, LastRow As Long, lRow As Long, i As Long: i = 1
    Set desWS = Sheets("Master Sheet")
    LastRow = desWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For Each ws In Sheets
        lRow = Range("C1:C" & LastRow).SpecialCells(xlCellTypeConstants).Areas(i).Cells(1).Row + 2
        If ws.Name <> "Master Sheet" Then
            ws.Range("B2", ws.Range("C" & Rows.Count).End(xlUp)).Copy desWS.Range("C" & lRow)
            i = i + 1
        End If
    Next ws
    Application.ScreenUpdating = True
End Sub

 

[isanka88]

Hi Mumps, You are amazing. This works 100% for my problem. Because of emerald person like you, I was able to replace the manual task which I have been doing for almost 2 years. It is almost out of verbal definitions to say thank you. Love you?

 

[mumps]

You are very welcome.