VBA adding formulas referencing to new sheet

[telpek]

Hi all,

I have a code which adds two new sheets ws2 and ws3 by the following script

Dim ws2 As Worksheet: Set ws2 = Sheets.Add

and it works fine.

I wrote a macro which adds all sort of formulas in both ws2 and ws3

But when I wanted to add formula to ws3 looking up values in ws2, the macro was unable to find the sheet ws2. I tried putting brackets around ws2 in the below code too, but the macro would not just locate ws2.

With ws3.Range("E4:E" & LastRow)
.Formula = "=VLOOKUP(A4,ws2!A:C,3,FALSE)"
End With

Please advise!

Thanks

 

[njimack]

Try this:
.Formula = "=VLOOKUP(A4," & ws2.Name & "!A:C,3,FALSE)"

 

[telpek]

Thank you, njimack!

 

[telpek]

Hi Njimack, could you please help me with a similar problem. The macro just does not like my formula

With ws3.Range("F4:F" & LastRow3)
.Formula = "=SUMIF(" & ws2.Name & " ! G:G, A:A ," & ws2.Name & " !H:H)"
End With

Thanks!

 

[telpek]

Same question was asked in https://stackoverflow.com/questions/49450042/sumif-formula-in-vba

 

[telpek]

Please do not answer as it was answered in the above forum. Thanks