Trying to insert a VLOOKUP using a vba but using variables as a sheet ref in formula

[h4wk1n545]

I currently have the following formula =IF(ISNUMBER(SEARCH("(",$O27)),VLOOKUP(LEFT($O27,4),'Tues Crews'!$B$6:$F$19,2,FALSE),VLOOKUP(Tues!$O27,'Tues Crews'!$B$6:$F$19,2,FALSE)) which i want to insert into a range using a VBA as part of a current macro the problem I am having is I want to use a reference for the tues crews sheet,

so would look something like this.

when I run the macro the sheet I am on becomes Home

=IF(ISNUMBER(SEARCH("(",$O27)),VLOOKUP(LEFT($O27,4),'Home " & Crews"'!$B$6:$F$19,2,FALSE),VLOOKUP(Tues!$O27,'Home " & Crews"'!$B$6:$F$19,2,FALSE))

the reason I want this is that I have a sheet for every day of the week and have slim lined the macro as much as my knowledge allows


any help will be much appreciated

 

[rakesh seebaruth]

Sub InsertFormulaInRange()
    Range("O27:O32").Formula = "=IF(ISNUMBER(SEARCH(""(",$O27"")),VLOOKUP(LEFT($O27,4),'Tues Crews'!$B$6:$F$19,2,FALSE),VLOOKUP(Tues!$O27,'Tues Crews'!$B$6:$F$19,2,FALSE))" 
    Range("O27:O32").FormulaReferenceStyle = xlA1
    Range("O27:O32").Calculate
End Sub

 

[h4wk1n545]

Sub InsertFormulaInRange()
    Range("O27:O32").Formula = "=IF(ISNUMBER(SEARCH(""(",$O27"")),VLOOKUP(LEFT($O27,4),'Tues Crews'!$B$6:$F$19,2,FALSE),VLOOKUP(Tues!$O27,'Tues Crews'!$B$6:$F$19,2,FALSE))"
    Range("O27:O32").FormulaReferenceStyle = xlA1
    Range("O27:O32").Calculate
End Sub

Sorry I don't think I explained what I'm looking for very well.

So the sheets i have in my work book are

Days :Tues, Wed, Thurs, Fri, Sat

Days Crews: Tues Crews, Wed Crews, Thurs Crews, Fri Crews , Sat Crews

each time I run my code I will be on any of the days sheets which will then set Home as the name of that sheet what i am trying to do is get my formula so that it will ref the Days crews sheet using 'Home " & Crews"'

 

[h4wk1n545]

so currently I have

Dim ws0 As Worksheet, ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet, ws4 As Worksheet
Dim DestRow As Long
Dim CountRow As Long
Dim Value As Long
Dim Val As Long
Dim Rng As Range
Set ws0 = Sheets("Input")
Set ws1 = Sheets("Templates")
Set ws2 = Sheets("Footprints")
Set ws3 = Sheets("Hold Print")
Set ws4 = Sheets("Pallet note")
Dim Home As String
Home = ActiveSheet.Name


DestRow = Sheets(Home).Cells(Rows.Count, "AO").End(xlUp).Row + 1 + Val
CountRow = Sheets(Home).Cells(Rows.Count, "E").End(xlUp).Row
Sheets(Home).Activate
Range("AO" & DestRow, "AO" & CountRow).FormulaR1C1 = "=IFERROR(IF(ISNUMBER(SEARCH(""("",RC15)),VLOOKUP(LEFT(RC15,4),'Tues Crews'!R6C2:R19C7,3,FALSE),VLOOKUP(Tues!RC15,'Tues Crews'!R6C2:R19C7,3,FALSE)),"""")"


what i am trying to do is reference the Red text using 'Home " & Crews"' but cant seem to work it out