VBA Insert Formula based on Worksheet Name

[daveasu]

I have a workbook with multiple worksheets. I need VBA to insert formulas based on the worksheet name.

For example: a formula that will reference a value on another sheet in the workbook.

The sheet names are assigned dynamically with a 6-digit account numbers added to the existing sheet names earlier in the macro.

I tried using Application.Caller, but received an error message.

If Wkb.Sheets(Sheets.Count).Name Like "*Supplies" ThenRange("G45").Value = "=SUM(G9:H43)"
ElseIf Sheets(Sheets.Count).Name Like "*Travel" Then
Range("B7:B14").Columns.AutoFit
' Range("B15").Value = "='" & Application.Caller.Worksheet.Name & "'!H37"
End If

 

[Coding4Fun]

I am not really clear on what you are trying to achieve, the way I read this....

If the last sheet in the workbook has "supplies" in the Sheets Name then you want to sum a range an put it in the ActiveSheet Range G45

Otherwise if the last sheet in the workbooks name end with "travel" then you want to make Range B15 of the ActiveSheet equal H37 from some other sheet?

What is this other sheet? How is it being determined or identified?