How to paste to the next empty space.

T

trprpfo

New Member
Joined
Dec 13, 2019
Messages
8
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
  2. Mobile
Hello everyone! I am new to this forum and was looking for some help. I have spent a couple of days trying to figure this out and spending hours on google to look for a solution. Here is my issue. I have code where I copy multiple columns if a condition is met. It works well until it searches the next worksheet then the information previously pasted will be replaced with the this worksheets info. I'm trying to find a way to use my existing code to make it work. I pull info from multiple worksheets and apply them to a template.

Below is the code I using from just one worksheet:

Code: 
Sub Weekly_Report_201()

Dim r As Range
Dim j As Integer
Dim Source As Worksheet
Dim Target As Worksheet

Workbooks.Open ("Y:\Operations\Shared\LDAR Data (Dry Gas)\Route 201 FLIR data.xlsx")


    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Cermak")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
         Target.Cells(j, "A").Value = Source.Cells(r.Row, "O").Value
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Cermak")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
        Target.Range("B22:B40").ClearContents
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "B").Value = Source.Cells(r.Row, "A").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Cermak")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
        Target.Range("C22:C40").ClearContents
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "C").Value = Source.Cells(r.Row, "B").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Cermak")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
        Target.Range("D22:D40").ClearContents
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "D").Value = Source.Cells(r.Row, "K").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Cermak")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
        Target.Range("E22:E40").ClearContents
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "E").Value = Source.Cells(r.Row, "E").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Cermak")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
        Target.Range("F22:F40").ClearContents
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "F").Value = Source.Cells(r.Row, "F").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
       Set Source = Workbooks("Route 201 FLIR data").Worksheets("Cermak")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "G").Value = Source.Cells(r.Row, "G").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
     Set Source = Workbooks("Route 201 FLIR data").Worksheets("Club")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "A").Value = Source.Cells(r.Row, "O").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Club")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "B").Value = Source.Cells(r.Row, "A").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Club")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "C").Value = Source.Cells(r.Row, "B").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Club")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "D").Value = Source.Cells(r.Row, "K").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Club")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "E").Value = Source.Cells(r.Row, "E").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Club")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "F").Value = Source.Cells(r.Row, "F").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
       Set Source = Workbooks("Route 201 FLIR data").Worksheets("Club")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "G").Value = Source.Cells(r.Row, "G").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
     Set Source = Workbooks("Route 201 FLIR data").Worksheets("Colaianni")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "A").Value = Source.Cells(r.Row, "O").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Colaianni")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "B").Value = Source.Cells(r.Row, "A").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Colaianni")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "C").Value = Source.Cells(r.Row, "B").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Colaianni")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "D").Value = Source.Cells(r.Row, "K").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Colaianni")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "E").Value = Source.Cells(r.Row, "E").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Colaianni")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "F").Value = Source.Cells(r.Row, "F").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
       Set Source = Workbooks("Route 201 FLIR data").Worksheets("Colaianni")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "G").Value = Source.Cells(r.Row, "G").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Creamer")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "A").Value = Source.Cells(r.Row, "O").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Creamer")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "B").Value = Source.Cells(r.Row, "A").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Creamer")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "C").Value = Source.Cells(r.Row, "B").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Creamer")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "D").Value = Source.Cells(r.Row, "K").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Creamer")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "E").Value = Source.Cells(r.Row, "E").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Creamer")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "F").Value = Source.Cells(r.Row, "F").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
       Set Source = Workbooks("Route 201 FLIR data").Worksheets("Creamer")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "G").Value = Source.Cells(r.Row, "G").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Dalrymple")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "A").Value = Source.Cells(r.Row, "O").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Dalrymple")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "B").Value = Source.Cells(r.Row, "A").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Dalrymple")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "C").Value = Source.Cells(r.Row, "B").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Dalrymple")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "D").Value = Source.Cells(r.Row, "K").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Dalrymple")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "E").Value = Source.Cells(r.Row, "E").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Dalrymple")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "F").Value = Source.Cells(r.Row, "F").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
       Set Source = Workbooks("Route 201 FLIR data").Worksheets("Dalrymple")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "G").Value = Source.Cells(r.Row, "G").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Darrow")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "A").Value = Source.Cells(r.Row, "O").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Darrow")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "B").Value = Source.Cells(r.Row, "A").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Darrow")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "C").Value = Source.Cells(r.Row, "B").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Darrow")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "D").Value = Source.Cells(r.Row, "K").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Darrow")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "E").Value = Source.Cells(r.Row, "E").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Darrow")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "F").Value = Source.Cells(r.Row, "F").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
       Set Source = Workbooks("Route 201 FLIR data").Worksheets("Darrow")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "G").Value = Source.Cells(r.Row, "G").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Elite")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "A").Value = Source.Cells(r.Row, "O").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Elite")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "B").Value = Source.Cells(r.Row, "A").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Elite")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "C").Value = Source.Cells(r.Row, "B").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Elite")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "D").Value = Source.Cells(r.Row, "K").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Elite")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "E").Value = Source.Cells(r.Row, "E").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Elite")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "F").Value = Source.Cells(r.Row, "F").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
       Set Source = Workbooks("Route 201 FLIR data").Worksheets("Elite")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "G").Value = Source.Cells(r.Row, "G").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Faldowski")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "A").Value = Source.Cells(r.Row, "O").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Faldowski")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "B").Value = Source.Cells(r.Row, "A").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Faldowski")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "C").Value = Source.Cells(r.Row, "B").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Faldowski")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "D").Value = Source.Cells(r.Row, "K").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Faldowski")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "E").Value = Source.Cells(r.Row, "E").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Faldowski")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "F").Value = Source.Cells(r.Row, "F").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
       Set Source = Workbooks("Route 201 FLIR data").Worksheets("Faldowski")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "G").Value = Source.Cells(r.Row, "G").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Ferguson")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "A").Value = Source.Cells(r.Row, "O").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Ferguson")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "B").Value = Source.Cells(r.Row, "A").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Ferguson")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "C").Value = Source.Cells(r.Row, "B").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Ferguson")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "D").Value = Source.Cells(r.Row, "K").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Ferguson")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "E").Value = Source.Cells(r.Row, "E").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Ferguson")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "F").Value = Source.Cells(r.Row, "F").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
       Set Source = Workbooks("Route 201 FLIR data").Worksheets("Ferguson")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "G").Value = Source.Cells(r.Row, "G").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Grywalski")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "A").Value = Source.Cells(r.Row, "O").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Grywalski")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "B").Value = Source.Cells(r.Row, "A").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Grywalski")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "C").Value = Source.Cells(r.Row, "B").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Grywalski")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "D").Value = Source.Cells(r.Row, "K").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Grywalski")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "E").Value = Source.Cells(r.Row, "E").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Grywalski")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "F").Value = Source.Cells(r.Row, "F").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
       Set Source = Workbooks("Route 201 FLIR data").Worksheets("Grywalski")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "G").Value = Source.Cells(r.Row, "G").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Jack Hamilton")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "A").Value = Source.Cells(r.Row, "O").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Jack Hamilton")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "B").Value = Source.Cells(r.Row, "A").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Jack Hamilton")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "C").Value = Source.Cells(r.Row, "B").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Jack Hamilton")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "D").Value = Source.Cells(r.Row, "K").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Jack Hamilton")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "E").Value = Source.Cells(r.Row, "E").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Jack Hamilton")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "F").Value = Source.Cells(r.Row, "F").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
       Set Source = Workbooks("Route 201 FLIR data").Worksheets("Jack Hamilton")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "G").Value = Source.Cells(r.Row, "G").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Lori")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "A").Value = Source.Cells(r.Row, "O").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Lori")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "B").Value = Source.Cells(r.Row, "A").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Lori")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "C").Value = Source.Cells(r.Row, "B").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Lori")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "D").Value = Source.Cells(r.Row, "K").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Lori")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "E").Value = Source.Cells(r.Row, "E").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Lori")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "F").Value = Source.Cells(r.Row, "F").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
       Set Source = Workbooks("Route 201 FLIR data").Worksheets("Lori")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "G").Value = Source.Cells(r.Row, "G").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Marquad")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "A").Value = Source.Cells(r.Row, "O").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Marquad")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "B").Value = Source.Cells(r.Row, "A").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Marquad")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "C").Value = Source.Cells(r.Row, "B").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Marquad")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "D").Value = Source.Cells(r.Row, "K").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Marquad")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "E").Value = Source.Cells(r.Row, "E").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Marquad")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "F").Value = Source.Cells(r.Row, "F").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
       Set Source = Workbooks("Route 201 FLIR data").Worksheets("Marquad")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "G").Value = Source.Cells(r.Row, "G").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Phillips")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "A").Value = Source.Cells(r.Row, "O").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Phillips")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "B").Value = Source.Cells(r.Row, "A").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Phillips")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "C").Value = Source.Cells(r.Row, "B").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Phillips")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "D").Value = Source.Cells(r.Row, "K").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Phillips")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "E").Value = Source.Cells(r.Row, "E").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Phillips")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "F").Value = Source.Cells(r.Row, "F").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
       Set Source = Workbooks("Route 201 FLIR data").Worksheets("Phillips")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "G").Value = Source.Cells(r.Row, "G").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Porco")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "A").Value = Source.Cells(r.Row, "O").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Porco")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "B").Value = Source.Cells(r.Row, "A").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Porco")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "C").Value = Source.Cells(r.Row, "B").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Porco")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "D").Value = Source.Cells(r.Row, "K").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Porco")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "E").Value = Source.Cells(r.Row, "E").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Porco")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "F").Value = Source.Cells(r.Row, "F").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
       Set Source = Workbooks("Route 201 FLIR data").Worksheets("Porco")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "G").Value = Source.Cells(r.Row, "G").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Rector")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "A").Value = Source.Cells(r.Row, "O").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Rector")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "B").Value = Source.Cells(r.Row, "A").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Rector")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "C").Value = Source.Cells(r.Row, "B").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Rector")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "D").Value = Source.Cells(r.Row, "K").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Rector")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "E").Value = Source.Cells(r.Row, "E").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Rector")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "F").Value = Source.Cells(r.Row, "F").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
       Set Source = Workbooks("Route 201 FLIR data").Worksheets("Rector")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "G").Value = Source.Cells(r.Row, "G").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Ronald")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "A").Value = Source.Cells(r.Row, "O").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Ronald")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "B").Value = Source.Cells(r.Row, "A").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Ronald")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "C").Value = Source.Cells(r.Row, "B").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Ronald")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "D").Value = Source.Cells(r.Row, "K").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Ronald")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "E").Value = Source.Cells(r.Row, "E").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Ronald")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "F").Value = Source.Cells(r.Row, "F").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
       Set Source = Workbooks("Route 201 FLIR data").Worksheets("Ronald")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "G").Value = Source.Cells(r.Row, "G").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Ruth")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "A").Value = Source.Cells(r.Row, "O").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Ruth")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "B").Value = Source.Cells(r.Row, "A").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Ruth")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "C").Value = Source.Cells(r.Row, "B").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Ruth")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "D").Value = Source.Cells(r.Row, "K").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Ruth")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "E").Value = Source.Cells(r.Row, "E").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Ruth")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "F").Value = Source.Cells(r.Row, "F").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
       Set Source = Workbooks("Route 201 FLIR data").Worksheets("Ruth")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "G").Value = Source.Cells(r.Row, "G").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Smithfield ")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "A").Value = Source.Cells(r.Row, "O").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Smithfield ")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "B").Value = Source.Cells(r.Row, "A").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Smithfield ")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "C").Value = Source.Cells(r.Row, "B").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Smithfield ")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "D").Value = Source.Cells(r.Row, "K").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Smithfield ")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "E").Value = Source.Cells(r.Row, "E").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Smithfield ")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "F").Value = Source.Cells(r.Row, "F").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
       Set Source = Workbooks("Route 201 FLIR data").Worksheets("Smithfield ")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "G").Value = Source.Cells(r.Row, "G").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Vincent")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "A").Value = Source.Cells(r.Row, "O").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Vincent")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "B").Value = Source.Cells(r.Row, "A").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Vincent")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "C").Value = Source.Cells(r.Row, "B").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Vincent")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "D").Value = Source.Cells(r.Row, "K").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Vincent")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "E").Value = Source.Cells(r.Row, "E").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
    Set Source = Workbooks("Route 201 FLIR data").Worksheets("Vincent")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "F").Value = Source.Cells(r.Row, "F").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
       Set Source = Workbooks("Route 201 FLIR data").Worksheets("Vincent")
        Set Target = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    j = 22
    For Each r In Source.Range("L2:L475")
         If r = "No" Then
            Target.Cells(j, "G").Value = Source.Cells(r.Row, "G").Value
                                                     
                j = j + 1
                    End If
                        Next r
                       
                       
   
ActiveWorkbook.Close SaveChanges:=False

Call Weekly_Report_202

End Sub

If anyone can help it would be appreciated. Thanks.
 
Last edited by a moderator:

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Sort by date Sort by votes
Hi & welcome to MrExcel
You can replace the first part of you code, dealing with the "Cermak" sheet with
VBA Code: 
Sub Weekly_Report_201()
    Dim r As Range
    Dim j As Integer
    Dim SourceWs As Worksheet
    Dim TargetWs As Worksheet
    
    Workbooks.Open ("Y:\Operations\Shared\LDAR Data (Dry Gas)\Route 201 FLIR data.xlsx")
    
    Set SourceWs = Workbooks("Route 201 FLIR data.xlsx").Worksheets("Cermak")
    Set TargetWs = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    
    TargetWs.Range("A22:G40").ClearContents
    j = 22
    For Each r In SourceWs.Range("L2:L475")
        If r = "No" Then
            TargetWs.Cells(j, "A").Value = SourceWs.Cells(r.Row, "O").Value
            TargetWs.Cells(j, "B").Value = SourceWs.Cells(r.Row, "A").Value
            TargetWs.Cells(j, "C").Value = SourceWs.Cells(r.Row, "B").Value
            TargetWs.Cells(j, "D").Value = SourceWs.Cells(r.Row, "K").Value
            TargetWs.Cells(j, "E").Value = SourceWs.Cells(r.Row, "E").Value
            TargetWs.Cells(j, "F").Value = SourceWs.Cells(r.Row, "F").Value
            TargetWs.Cells(j, "G").Value = SourceWs.Cells(r.Row, "G").Value
            j = j + 1
        End If
    Next r
    
     Set SourceWs = Workbooks("Route 201 FLIR data").Worksheets("Club")
Where do you want to put the data from the other sheets?
 
Upvote 0
Fluff said:
Hi & welcome to MrExcel
You can replace the first part of you code, dealing with the "Cermak" sheet with
VBA Code: 
Sub Weekly_Report_201()
    Dim r As Range
    Dim j As Integer
    Dim SourceWs As Worksheet
    Dim TargetWs As Worksheet
   
    Workbooks.Open ("Y:\Operations\Shared\LDAR Data (Dry Gas)\Route 201 FLIR data.xlsx")
   
    Set SourceWs = Workbooks("Route 201 FLIR data.xlsx").Worksheets("Cermak")
    Set TargetWs = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
   
    TargetWs.Range("A22:G40").ClearContents
    j = 22
    For Each r In SourceWs.Range("L2:L475")
        If r = "No" Then
            TargetWs.Cells(j, "A").Value = SourceWs.Cells(r.Row, "O").Value
            TargetWs.Cells(j, "B").Value = SourceWs.Cells(r.Row, "A").Value
            TargetWs.Cells(j, "C").Value = SourceWs.Cells(r.Row, "B").Value
            TargetWs.Cells(j, "D").Value = SourceWs.Cells(r.Row, "K").Value
            TargetWs.Cells(j, "E").Value = SourceWs.Cells(r.Row, "E").Value
            TargetWs.Cells(j, "F").Value = SourceWs.Cells(r.Row, "F").Value
            TargetWs.Cells(j, "G").Value = SourceWs.Cells(r.Row, "G").Value
            j = j + 1
        End If
    Next r
   
     Set SourceWs = Workbooks("Route 201 FLIR data").Worksheets("Club")
Where do you want to put the data from the other sheets?
Click to expand...
The next sheet will go the same place the Cermak goes but I would like the next sheet to paste right under the first.
 
Upvote 0
Ok, how about
VBA Code: 
Sub Weekly_Report_201()
    Dim r As Range
    Dim j As Long, i As Long
    Dim Ary As Variant
    Dim TargetWs As Worksheet
    Dim Wbk As Workbook
    
    Ary = Array("Cermak", "Club", "Colaianni", "Creamer", "Dalrymple", "Darrow", "Elite", "Faldowski", "Ferguson", "Grywalski", _
                      "Jack Hamilton", "Lori", "Marquad", "Phillips", "Porco", "Rector", "Ronald", "Ruth", "Smithfield", "Vincent")

    Set Wbk = Workbooks.Open("Y:\Operations\Shared\LDAR Data (Dry Gas)\Route 201 FLIR data.xlsx")
    
    Set TargetWs = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
    
    j = 22
    For i = 0 To UBound(Ary)
        With Wbk.Worksheets(Ary(i))
            For Each r In .Range("L2:L475")
                If r = "No" Then
                    TargetWs.Cells(j, "A").Value = .Cells(r.Row, "O").Value
                    TargetWs.Cells(j, "B").Value = .Cells(r.Row, "A").Value
                    TargetWs.Cells(j, "C").Value = .Cells(r.Row, "B").Value
                    TargetWs.Cells(j, "D").Value = .Cells(r.Row, "K").Value
                    TargetWs.Cells(j, "E").Value = .Cells(r.Row, "E").Value
                    TargetWs.Cells(j, "F").Value = .Cells(r.Row, "F").Value
                    TargetWs.Cells(j, "G").Value = .Cells(r.Row, "G").Value
                    j = j + 1
                End If
            Next r
        End With
    Next i

    Wbk.Close SaveChanges:=False

    Call Weekly_Report_202

End Sub
 
Upvote 0
Fluff said:
Ok, how about
VBA Code: 
Sub Weekly_Report_201()
    Dim r As Range
    Dim j As Long, i As Long
    Dim Ary As Variant
    Dim TargetWs As Worksheet
    Dim Wbk As Workbook
   
    Ary = Array("Cermak", "Club", "Colaianni", "Creamer", "Dalrymple", "Darrow", "Elite", "Faldowski", "Ferguson", "Grywalski", _
                      "Jack Hamilton", "Lori", "Marquad", "Phillips", "Porco", "Rector", "Ronald", "Ruth", "Smithfield", "Vincent")

    Set Wbk = Workbooks.Open("Y:\Operations\Shared\LDAR Data (Dry Gas)\Route 201 FLIR data.xlsx")
   
    Set TargetWs = Workbooks("MasterFLIRData").Worksheets("Weekly Report")
   
    j = 22
    For i = 0 To UBound(Ary)
        With Wbk.Worksheets(Ary(i))
            For Each r In .Range("L2:L475")
                If r = "No" Then
                    TargetWs.Cells(j, "A").Value = .Cells(r.Row, "O").Value
                    TargetWs.Cells(j, "B").Value = .Cells(r.Row, "A").Value
                    TargetWs.Cells(j, "C").Value = .Cells(r.Row, "B").Value
                    TargetWs.Cells(j, "D").Value = .Cells(r.Row, "K").Value
                    TargetWs.Cells(j, "E").Value = .Cells(r.Row, "E").Value
                    TargetWs.Cells(j, "F").Value = .Cells(r.Row, "F").Value
                    TargetWs.Cells(j, "G").Value = .Cells(r.Row, "G").Value
                    j = j + 1
                End If
            Next r
        End With
    Next i

    Wbk.Close SaveChanges:=False

    Call Weekly_Report_202

End Sub
Click to expand...
When running the code it stops at "With Wbk.Worksheets(Ary(i))".
 
Upvote 0
trprpfo said:
When running the code it stops at "With Wbk.Worksheets(Ary(i))".
Click to expand...
Update: When I manually scroll through the code it works perfect but when I run automatically it stops at the above line.
 
Upvote 0
What is the error number & message you get?
 
Upvote 0
That means that one of those sheets doesn't exist in the "Route 201 FLIR data.xlsx" workbook.
Check the spelling and also check for leading/trailing spaces.
 
Upvote 0
Fluff said:
That means that one of those sheets doesn't exist in the "Route 201 FLIR data.xlsx" workbook.
Check the spelling and also check for leading/trailing spaces.
Click to expand...
That was it. One of the names had a space after it. Code works like a charm. Thank you very much.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
Output text vba should run10000 rows batch wise on every run till it ends.
Replies
0
Views
127
Sathish G
S
I
  • Question
VBA: Search for keywords in data strings and perform actions with that data
Replies
3
Views
335
Peter_SSs
Peter_SSs
S
  • Question
Worksheet Change Event
Replies
2
Views
380
sspatriots
S
S
  • Question
Copy a column value when the values match to multiple sheets of the workbook
Replies
1
Views
234
sofas
S
H
  • Solved
Excel VBA file importing data from several files located in same folder -> challenge to copy data only (rest is working perfectly)
Replies
3
Views
137
NdNoviceHlp
NdNoviceHlp

Forum statistics

Threads
1,215,008
Messages
6,122,672
Members
449,091
Latest member
peppernaut

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top