Writing a Macro to Combine Specific Sheets

[JMcAnarney]

We are trying to write a macro to combine data on multiple sheets throughout a workbook.  We are having trouble because we cannot figure out how to omit certain pages of the book.  For example we want columns A:CQ added with rows 2:225 taken as part of the data.   What we are trying to do is combine 5 sheets with cells A2 to CQ225 all onto one data tab.  We attached an example of what we are trying to use but cannot figure out how to only include sheets specifically name in the Macro

[#2]

@JMcAnarney 

The crux is in this line:

IF ws.Name <> "Master" Then

If there are just one or two other exceptions you add them like so:

IF ws.Name <> "Master" And ws.Name <> "OtherName" And ws.Name <> "AnotherName" Then

[#3]

@Jan Karel Pieterse 

Thanks I am trying to get it where it just does the values like the following:

Thanks I tried to do it like the following since there are many sheets and many rows of data:

Sub CombineData()

Dim ws As Worksheet, Nws As Worksheet

If Evaluate("isref('Data'!B2)") Then
Set Nws = Sheets("Data")
Else
Set Nws = Sheets.Add(Sheets(1))
Nws.Name = "Data"
End If
For Each ws In Worksheets
If ws.Name <> "Data" And ws.Name <> "Workforce File" Then
Nws.Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(95, 200).Value = ws.Range("b2:cq295").Value
End If
Next ws
End Sub

Basically I want it too combine all the data in in columns A to CQ all the way down it only combines about half of the data I attached a sample workbook to where the highlight tabs are the ones where I want all the data combined and I want to switch the script up to where it is slecting sheets I DO want as opposed to the negatives.  In this case I Want police and fire and general ftes

appreciate the help

[#4]

@JMcAnarney Here's your macro catering for including headings and for the different worksheet sizes

Sub CombineData()
    Dim ws As Worksheet, Nws As Worksheet
    Dim data As Variant
    Dim hasHeaders As Boolean
    If Evaluate("isref('Data'!B2)") Then
        Set Nws = Sheets("Data")
        hasHeaders = True
    Else
        Set Nws = Sheets.Add(Sheets(1))
        Nws.Name = "Data"
    End If
    For Each ws In Worksheets
        If ws.Name <> "Data" And ws.Name <> "Workforce File" Then
            If hasHeaders Then    'first time we want the header row
                data = ws.UsedRange.Offset(1).Value2
            Else
                data = ws.UsedRange.Value2
            End If
            If IsArray(data) Then    'Sheet might be empty!
                If hasHeaders Then
                    Nws.Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(UBound(data, 1), UBound(data, 2)).Value = data
                Else
                    Nws.Range("A" & Rows.Count).End(xlUp).Resize(UBound(data, 1), UBound(data, 2)).Value = data
                End If
                hasHeaders = True    'No more headers needed
            End If
        End If
    Next ws
End Sub