vba use cell value to select sheet within formula

[twday]

Is there a way I can us strName in place of 'X009516'! so that I can have the macro pull from a user designated (entered into M5) sheet?

Dim strName As String
strName = Range("M5")
    
    Range("B1").Select
    Selection.End(xlDown).Offset(1).Select
    ActiveCell.Formula = "='X009516'!B1"
    Selection.Offset(0, 1).Select
    ActiveCell.Formula = "='X009516'!C9"
    Selection.Offset(0, 1).Select

Thanks in advance for any advice!

 

[ranman256]

ActiveCell.Formula = "=" & Sheets(strName).Name & "!B1"

 

[AlphaFrog]

Try this...

ActiveCell.Formula = "='" & strName & "'!C9"


Alternatively, you could put this formula in cell B1
=INDIRECT("'"&M5&"'!B1")

 

[twday]

Excellent!
Both suggestions worked like a charm!

Thanks for the help.