copy data from a number of sheets but avoid others in to Master sheet and then remove duplicates in master

[novice_at_VBA]

Firstly i apologise if this has been answered before but i am getting myself more confused the more i look.

I am trying to set a macro / vba up to copy data from about 74 sheets and paste into a sheet called "Master", the problem i have is that i have a number of other sheets I do not want to obtain data from, these are "Summary sheet", "individual Monthly data", "individual continued", "YTD Data Sheet" & "Hidden data"

The sheets i want to copy from may or may not have data in them and the amount of rows can vary so i want something that looks until a blank row if that makes sense? The data will appear on row 7 (if there is any data) and i need to copy Cell B7 and C7 on the row and any below but if blank no copy needed. (both cells can contain mixed data text&numbers)

The other piece of help needed would be to remove the duplicates in the master sheet once data has been obtained.

I found some coding and tried to alter it but clearly it will not do what i need at the moment (in master sheet I would like data in column A & B with a header

Sub CombineData()Dim Sht As Worksheet
For Each Sht In ActiveSheet.Worksheets
If Sht.Name <> "Master" And Sht.Range("b7").Value <> "" Then
ActiveSheet.Select
Lastrow = Range("A65536").End(xlUp).Row
Range("B7", Cells(Lastrow, "C")).Copy
Sheets("Master").Select
Range("A65536").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
ActiveSheet.Next.Select

Thank you in advance

Total Novice

 

[Fluff]

Hi & welcome to the board.
Give this a go

Sub CopyShtsData()

    Dim Arr As Variant
    Dim Sht As Worksheet
    Dim UsdRws As Long
    
    Arr = Array("[COLOR=#ff0000]Summary sheet[/COLOR]", "[COLOR=#ff0000]individual Monthly data[/COLOR]", "[COLOR=#ff0000]individual continued[/COLOR]", "[COLOR=#ff0000]Master[/COLOR]", "[COLOR=#ff0000]YTD Data Sheet[/COLOR]", "[COLOR=#ff0000]Hidden data[/COLOR]")
    With Sheets("Master")
        .Range("A1").Value = "Header1"
        .Range("B1").Value = "Header2"
    End With
    For Each Sht In Worksheets
        If Not UBound(Filter(Arr, Sht.Name, True)) >= 0 Then
            UsdRws = Sht.Range("B" & Rows.Count).End(xlUp).Row
            If UsdRws > 7 Then
                Sht.Range("B7:C" & UsdRws).Copy Sheets("Master").Range("A" & Rows.Count).End(xlUp).Offset(1)
            End If
        End If
    Next Sht

End Sub

Bearing in mind that the sheet names to exclude (in red) must be spelt exactly right, including correct case.

 

[Fluff]

Forgot the part about duplicates.
If you had data as in Col A, would you expect the result shown in Col B, or Col C

Excel 2013 32 bit
	A	B	C
1	Excel	Excel	Excel
2	VBA	VBA	Fluff
3	Fluff	Fluff
4	VBA
Site

 

[novice_at_VBA]

Fluff,

Thanks for this, I wasn't expecting such a quick response. Do you mind giving me some additional information just so I can get my head around how you have worked it,
1. Dim Arr As Variant - does this mean if I add new sheets at a later date and I want them excluded all I do is add the sheet name and do not need to alter anything else?
2. Dim UsdRws As Long - what is this and what is meant by long?
3. If Not UBound - what is Ubound and how does it work?
If you don't get time to answer don't worry.
Thanks again

 

[Fluff]

Fluff,

Thanks for this, I wasn't expecting such a quick response. Do you mind giving me some additional information just so I can get my head around how you have worked it,

1. Dim Arr As Variant - does this mean if I add new sheets at a later date and I want them excluded all I do is add the sheet name and do not need to alter anything else? Yes that's right, as long as you enter them in the same style.
2. Dim UsdRws As Long - what is this and what is meant by long? This simply declares the variable UsdRws & specifies that it is of the Long integer type. (UsdRws is the same as Lastrow that you used in your code, just a different naming convention)
3. If Not UBound - what is Ubound and how does it work? it stands for Upper Bound (the upper limit in an array) If the sheet name is in the array (ie one of those to be excluded) then Ubound will return a value >=0 so that sheet gets ignored

See here for more about variables & declaring them
http://www.excelfunctions.net/VBA-Variables-And-Constants.html
HTH