Excel VBA Worksheet.Function with Dynamic Worksheet Reference
<p>I have an Excel file (wbTL) with the Main WS(INSTRUCTIONS) for analysis and many other WS with data for each Country (that will be analyse in the Main Sheet)
Main sheet has a Table with all the Countries and displays critical information for each country using Functions with References to the Country Sheet in question.</p>

<p>The information in the Countries WS is sent from Other Excel (wb).</p>

<p>My Problem :</p>

<p>Let's say there is a new country. 
User will report the Information from (wb) and in the (wbTL) a new Worksheet will be created with a specific name.</p>

<p>Now I want to update the Table with the new Country Name and the functions should make reference to the new Worksheet Generate.</p>

<pre><code>Sub Test()

Dim wb As Workbook, wbTL As Workbook
Dim wsRep As Worksheet, wsIns As Worksheet, wsInsTL As Worksheet
Dim lrowTable As Long, lcolTable As Long
Dim rngTLPath As Range
Dim CountryInList As Single
Dim s As String, j As String
Dim rngTable As Range

Set wb = ThisWorkbook
Set wsIns = wb.Worksheets("INSTRUCTIONS")
Set rngTLPath = wsIns.Range("TLPATH")
Set wbTL = Workbooks.Open(rngTLPath)
Set wsRep = wb.Worksheets("REPORT")
Set wsInsTL = wbTL.Worksheets("INSTRUCTIONS")

lrowTable = wbTL.Worksheets("INSTRUCTIONS").Cells(Rows.Count, 3).End(xlUp).Row
lcolTable = wbTL.Worksheets("INSTRUCTIONS").Cells(6, Columns.Count).End(xlToLeft).Column

Set rngTable = wsInsTL.Range(Cells(5, 3), Cells(lrowTable, lcolTable))
'What I pretend is to storage the variable S as string with the Worksheet for the country.
s = wsIns.Range("A1") 'I make a reference to a cell, lets say it says DENMARK
</code></pre>

<p>'Here I still working it out. I want the code to check if the country is already in the table.
'If the country is missing then Add a New Line with the Country/Functions.</p>

<pre><code>On Error Resume Next
CountryInList = Application.WorksheetFunction.VLookup(s, rngTable, 1, False)
On Error GoTo 0

If CountryInList = 0 Then
    wsInsTL.Cells(lrowTable + 1, 3).Value = s 'This does not work, leaves cell as Empty
</code></pre>

<p>'Here is my Main Issue :
'What I want to do get is Reference to the new WS generated according to the country Name
'What I was expecting is it to take "s" as string and generate :
=COUNTA('DENMARK'!A:A) - 1</p>

<pre><code>    wsInsTL.Cells(lrowTable + 1, 4).Formula = "=Counta('s'!A:A) - 1"
    wsInsTL.Cells(lrowTable + 1, 7).Formula = "=Countif('s'!$H:$H,Vlookup(G$5,OTHER!$N$1:$O$10,2,false))"
End If
End Sub
</code></pre>

<p>I have tried (adding "s" or double ""s"", deleting '', creating the variable string with "" or '' and ''"")</p>

<p>Is it possible to do it? or is there any workaround?</p>

<p>Many thanks in advance.</p>