Getting data from multiple tabs onto a master tab

[jmotola]

Hi,
I'm trying to accomplish the following:
Get data from multiple tabs in a specific cell range (B3-E169).
Data would be compiled in columns...i.e., column B copies into column B on new spreadsheet, column C copies into column C on new spreadsheet, etc.
If there are more than two blank columns in B, stop & move onto next worksheet and get same data.
The result that I'm trying to get is a material list from different categories (hence the different tabs).

This is how I think it should work:
Lookup WS1 column B3-B169.
If two blank columns, Lookup WS2 column B3-B169,
If two blank columns, Lookup WS3 column B3-B169, etc.

WS2 should skip a space/column & come in underneath WS1.
WS3 should skip a space/column & come in underneath WS2,.etc.

Repeat for columns C, D & E

I have tried the VBA code available here but I'm not sure how to modify to my application: Merge cells from all or some worksheets into one Master sheet

Tabs I need searched are below:

Sample data below:

As you can see, I leave a space between different items & there should also be a space between data from different tabs.
If there is more than one space between rows, then it should start searching the next tab.
Also, I do have an ActiveX Checkbox that allows me to choose which tabs show & which tabs are hidden.
It can disregard hidden tabs.

Thanks in advance ?

 

[DanteAmor]

Hi and welcome to MrExcel.

Create a sheet with the name "Master". Check if the following helps you

Sub Getting_data_from_multiple_tabs()
  Dim sh1 As Worksheet, sh2 As Worksheet
  Dim lr1 As Long, lr2 As Long
 
  Set sh1 = Sheets("Master")
  For Each sh2 In Sheets
    If sh2.Visible = xlSheetVisible And sh2.Name <> sh1.Name Then
      lr1 = sh1.Range("B" & Rows.Count).End(3).Row + 1
      lr2 = WorksheetFunction.Max(sh2.Range("B" & Rows.Count).End(3).Row, 3)
      sh1.Range("B" & lr1).Resize(lr2 - 2, 4).Value = sh2.Range("B3:E" & lr2).Value
    End If
  Next
End Sub