Copy same specific cells from only certain worksheets

[Corky112]

Hello,

I am very new and inexperienced at VBA & thank you to anyone and everyone for advice with this problem.

I worked on this macro, but I have a few challenges I'm hoping someone here can help with.

How do I tell he loop to only go though so many worksheets, I have pivot tables at the back of the workbook I don't want or need copied.

Is it possible to start with the first sheet in the workbook end the loop when it 'finds' the "Master" tab so I can keep my support files behind and have them not copied?



Sub CombineData()
Dim ws As Worksheet
Application.ScreenUpdating = False

For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> "Master" Then
ws.Range("A17:N154").Copy
Sheets("Master").Cells(Rows.Count, "A").End(xlUp).Offset(1).PasteSpecial xlPasteValues
End If

Next
Application.ScreenUpdating = True

End Sub

 

[jolivanes]

You don't want the "Master" sheet included I assume. If you do, delete the "- 1"

For i = 1 To Worksheets("Master").Index - 1
Sheets(i).Range("A17:N154").Copy
etc etc

 

[JLGWhiz]

Let's say you have 20 sheets in a workbook with the last 5 sheets not being relevant to what you want the code to do, and the master sheet could be anywhere in the order of the first 15 sheets.

For i = 1 To 15
    If Sheets(i).Name <> "master" Then
        'Code to do stuff here
    End If
Next

Assume same set up with sheets but there are a couple of sheets you do not want to execute on in the first 15, name escept1 and except2.

For i = 1 To 15
    If Sheets(i).Name <> "master"  And Sheets(i).Name <> "except1" And Sheets(i).Name <> "except2" Then
        'Code to do stuff here
    End If
Next

You would use Sheets(i) to reference the current sheet you are working with.
If you have more than two or three exceptions within the group you want to work, you might consider moving the sheets to a different position simply by dragging them to the right with the mouse pointer. Of course, some companies have restrictions on changing the arrangement of files because it can cause problems with formulas in the sheets.

 

[jolivanes]

Try this

Sub Maybe()
Dim i As Long, shM As Worksheet
Set shM = Worksheets("Master")
    For i = 1 To ThisWorkbook.Worksheets("Master").Index - 1
        shM.Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(138, 14).Value = Sheets(i).Range("A17:N154").Value
    Next i
End Sub

 

[jolivanes]

or if you want to exclude sheets wherever they are located and no matter how many sheets in the workbook

Sub Maybe_B()
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        Select Case ws.Name
            Case "Master", "Sheet9", "Sheet10"   '<----- here all the sheet names to be excluded
                'do nothing
            Case Else
                Sheets("Master").Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(138, 14).Value = ws.Range("A17:N154").Value
        End Select
    Next ws
End Sub

 

[jolivanes]

None of the suggestions worked? We have not heard anything about it.

 

[JLGWhiz]

None of the suggestions worked? We have not heard anything about it.

Probably off work for the weekend. Check back on Monday.

 

[jolivanes]

I guess I forget about weekends nowadays. Every day is Sunday!!!
Won't be long where every person I know will be a new face I've never met before.
I have this terrible habit of expecting people to be thankful.
Thanks JLGWhiz

 

[JLGWhiz]

I guess I forget about weekends nowadays. Every day is Sunday!!!
Won't be long where every person I know will be a new face I've never met before.
I have this terrible habit of expecting people to be thankful.
Thanks JLGWhiz

Been there, done that. I am now at the stage where I don't know anybody because I have outlived everybody I once knew. Even if it is somebody I know, I probably won't remember their name. But it is nice living in my own world.