refer to an sheetnumber rather than name in a formula

[grapevine]

I have a snippet of code which need to look at the values on sheet 1 and lookup a value to insert onto various other sheets that are being created. This code is part of a much longer piece of code which is manipulating this worksheet and adding other sheets to it. the name of the worksheet will change everytime I run the macro as it will always be a new spreadsheet.

My problem is how to refer to the first sheet in the workbook as the sheet name is variable. The first formulas works, but is refering to the current name of the first sheet in the workbook


<code>
=IF(ISERROR(VLOOKUP(R[15]C[1],JN00104!C[-1]:C[11],5,FALSE)), "",VLOOKUP(R[15]C[1],JN00104!C[-1]:C[11],5 ))
</code>

This snippet is my attempt to try to refer the lookup to look at sheet 1 instead of a name, also I dont know how many rows will be in the sheet so I have also tried to amend to look at the whole columns

<code>

Range("B4").FormulaR1C1 = "=IF(ISERROR(VLOOKUP(R[15]C[1],INDIRECT("sheet1!A:C"),5,FALSE)), "",VLOOKUP(R[15]C[1],INDIRECT("sheet1!A:C"),5 ))"
</code>

I am getting an error and if anyone could point me in the right direction I would be so grateful.
Many thanks

 

[Trevor G]

Check out Chip Pearson website to see if it helps

http://www.cpearson.com/excel/sheetref.htm

 

[grapevine]

formula not recognised

Thank you for referring me to the website above, but I am still having problems. I have inserted the function further down my module
<code>
Function FirstSN() As String
Application.Volatile True
With Application.Caller.Parent.Parent.Worksheets
FirstSN = .Item(1).Name
End With
End Function
</code>

my formula now looks as follows:
<code>
Range("B4").FormulaR1C1 = "=vLOOKUP(R[15]C[1],INDIRECT(FirstSN() & "!A:E"),5)"
</code>

The formula is not recognised as it is highlighted in red, I did try double quotations and it ran but I do not get any results.
I did not understand what it meant on the web site about Parent properties. I am running this macro from a spreadsheet which is not modified. It looks at three workbooks which will have already have been opened and all have the same name but different suffixes.(the name will everytime the macro runs). I am swopping between the spreadsheets without any problems, but the focus is moving from one spreadsheet to another and I dont know if this is affecting anything.
The complete code for this macro is as follows (in case this helps)

<code>
Sub ExtractInternalOrders()
Dim ws As Worksheet
Dim FSN

FSN = Sheets(1).Name
For Each ws In ActiveWorkbook.Worksheets
ws.Activate

If Right(ActiveSheet.Name, 4) = "mSTs" Then
'Workbooks(FSN & "." & "HDR").Sheets("Sheet1").Copy After:=Workbooks(FSN & "." & "HDR").Sheets("Sheet1")
'Windows(FSN & "." & "dtl").Activate
Range("a1").Select
Do Until ActiveCell.Offset(0, 1) = "Grand Total"


ActiveCell.End(xlDown).Offset(1, 1).Select
If Right(ActiveCell, 5) = "Total" Then
Workbooks(FSN & "." & "HDR").Sheets("Sheet1").Copy After:=Workbooks(FSN & "." & "HDR").Sheets("Sheet1")
Windows(FSN & "." & "dtl").Activate
ActiveCell.Offset(-1, 0).Copy Destination:=Workbooks(FSN & "." & "HDR").Worksheets(3).Range("c19") ' copies purchase order number
Windows(FSN & "." & "HDR").Activate
Workbooks(FSN & "." & "HDR").Worksheets(3).Name = Range("c19").Value
Windows(FSN & "." & "dtl").Activate
ActiveCell.Offset(-1, 19).Select 'move to EACS ADH cell

If ActiveCell.Value <> "" Then ' check whether EACS ADH in cell in column U or not
ActiveCell.Offset(1, -4).Copy 'if yes copy and paste special total tickets
Workbooks(FSN & "." & "HDR").Worksheets(3).Range("b11").PasteSpecial xlPasteValues
ActiveCell.Offset(0, -3).Select ' move to EACS Main Cell
If ActiveCell.Value <> "" Then ' check whether EACS MAIN in cell or not
ActiveCell.Offset(1, -1).Copy 'if yes copy and paste special total tickets
Workbooks(FSN & "." & "HDR").Worksheets(3).Range("b12").PasteSpecial xlPasteValues
ActiveCell.Offset(0, 1).Select ' goes to W1, W2, W3 cell
If ActiveCell.Value <> "" Then ' check whether W1, W2, W3 in cell or not
ActiveCell.Copy Destination:=Workbooks(FSN & "." & "HDR").Worksheets(3).Range("d13") 'if yes copy and paste special total tickets
ActiveCell.Offset(1, -2).Copy 'if yes copy and paste special total tickets
Workbooks(FSN & "." & "HDR").Worksheets(3).Range("b13").PasteSpecial xlPasteValues
ActiveCell.Offset(2, -18).Select 'go to beginning of blank line

Windows(FSN & "." & "HDR").Activate
'Range("B4").FormulaR1C1 = "=IF(ISERROR(VLOOKUP(R[15]C[1],INDIRECT(Workbooks(FSN & ""."" & ""HDR"").Worksheets(3).Range(""a:d""))2,5,FALSE)), """",VLOOKUP(R[15]C[1],INDIRECT(Workbooks(FSN & ""."" & ""HDR"").Worksheets(3).Range(""a:d"")),5))" '("'D:\[Database"&B1&"]Sheet1'!A:C"),
Range("B4").FormulaR1C1 = "=vLOOKUP(R[15]C[1],INDIRECT(FirstSN() & "!A:E"),5)"
Range("D13").Select
With Selection.Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
Range("c19").Select
With Selection.Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
Range("D13").Select
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
End With

Windows(FSN & "." & "dtl").Activate


End If 'closing if for W1, W2, W3
End If 'closing if for EACS Main queston
End If 'closing if for EACS ADH question
End If 'closing if for does it say total
Loop

End If 'closing if for looking for sheets ending with ST's
Next ws
' Else:

End Sub
</code>