Consolidate data from multiple ranges from specific worksheets

[Negi1984]

hi All,

I have a workbook having more than 30 worksheets and increasing on regular basis.
Now every time I have combine data from specific worksheets and multiple ranges based on multiple request from my seniors.

for example : -
1) Sometimes I have to consolidate data from sheet name start with "BKH" or sometimes sheet name start with "AKH" or sometime for "BKH" & "AKH" both. Everytime I am combine it manually.
2) 99% of cases I have to copy data from range of column "B" to "G". and this section is divided into 1-5 sections. and some time from "A" to "F".
3) All sections header contains headers like "item", Item name" or "part number".(here needs to check every time if the range is from A to F or B to G based on first header.
4) while consolidation of all sheets, I need to add sheet name in consolidate sheet.
5) Also I need to add another column while consolidation which contains the "section name" which we find 1 row above each header in each section.

I am also attaching sample file with required result in "Consolidate Data". where data consolidated based on "BKH" only.

Link

Can any body suggest me VBA macro code for the same ?

Thanks in advance.

 

[mumps]

How do you determine which sheets to use for consolidation?

 

[Negi1984]

How do you determine which sheets to use for consolidation?

For this if any message box pop up show to ask which sheets data needs to combine or I can create a table in sheet where I can type which sheet name I have to combine both are ok for me.

 

[mumps]

Click here to download your file. Just click the "Copy Data" button.

 

[Negi1984]

Hi Mumps,

Thanks a lot for your valuable support. just want to looking for small modification instead of each sheet name I need only first 3 letters in pop up window.
and that macro needs to combine data for all the sheets whichever start with 3 letter.

 

[mumps]

Replace all the macros in the userform code module with the macro below:

Private Sub CommandButton1_Click()
    Application.ScreenUpdating = False
    Dim c As MSForms.Control, LastRow As Long, i As Long, CFnd As Long, desWS As Worksheet, ws As Worksheet
    Set desWS = Sheets("Consolidate Data")
    For Each c In Me.Controls
        If TypeName(c) = "CheckBox" Then
            If c.Value = True Then
                For Each ws In Sheets
                    If ws.Name Like "*" & c.Caption & "*" Then
                        With ws
                            LastRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
                            CFnd = .Cells.Find("section", LookIn:=xlValues, lookat:=xlPart).Column
                            With .Range(.Cells(2, CFnd), .Cells(LastRow, CFnd)).SpecialCells(xlCellTypeConstants)
                                For i = 1 To .Areas.Count
                                    FR = .Areas.Item(i).Row
                                    cnt = .Areas.Item(i).Cells.Count
                                    With desWS
                                        ws.Cells(FR + 2, CFnd).Resize(cnt - 2, 6).Copy .Cells(.Rows.Count, "C").End(xlUp).Offset(1)
                                        .Cells(.Rows.Count, "A").End(xlUp).Offset(1).Resize(cnt - 2) = ws.Name
                                        .Cells(.Rows.Count, "B").End(xlUp).Offset(1).Resize(cnt - 2) = Mid(Cells(FR, CFnd), 11, 9999)
                                    End With
                                Next i
                            End With
                        End With
                    End If
                Next ws
            End If
        End If
    Next c
    Application.ScreenUpdating = True
    Unload Me
End Sub

You will need only this one macro.

 

[Negi1984]

Hi Mumps,

I have deleted all the macro from user form code and replace with the above one. but when I am running the above code its not showing first 3 letters of sheet.

 

[mumps]

Click here to download the file.

 

[Negi1984]

Click here to download the file.

Thanks a ton mumps for your valuable support. it will really help me a lot .

 

[mumps]

You are very welcome.