Hi I currently have a set of data like this

Z AA
1| file Path number
2| location 1 1
3| location 2 2
4| location 3 1
5| location 4 1
6| location 5 2
7| location 6 1
8| location 7 2
9| location 8 1

there are loads more lines than this but this is for a demonstrative purpose there is also a sheet name in cell F2

this table is in setupsheet,

what I want to do is if the value in column AA is 1 i want to pull up the worksheet based off of the location (location 1, location 2 etc) in the form W:\everyone.......filename.xlsx

go to a sheet in the newly opened workbook which will match the name in F2 in the first sheet copy a selected range (a6:q26) (F2 will change)

then go back to the original workbook and copy it to a sheet named datasheet in the next available line,

then repeat this for each of the rows where AA = 1

I currently have this 
Sub Button3_Click()
'Define Dims
Dim SetupSheet As Worksheet
Dim DataSheet As Worksheet
Dim i As Integer
Dim SiteName As String
Dim FinalRow As Integer
Dim LastRow As Integer
Dim FileName As Workbook


'Set Values
Set SetupSheet = Sheet1
Set DataSheet = Sheet3
SiteName = SetupSheet.Range("F2").Value

'Clear DataSheet
DataSheet.Range("A1:Z10000").ClearContents

'Define FinalRow to get a count list
SetupSheet.Select
FinalRow = Cells(Rows.Count, 27).End(xlUp).Row

For i = 2 To FinalRow

    If Cells(i, 27) = 1 Then
    
    
     
    End If
    Next i
    
    

End Sub
send help please

Kind Regards,

T