Excel VBA - Creating a macro that inserts the active sheet name inside another sheet cell's forumula

Question

I'm working with two sheets inside my workbook. The "active sheet" and my "Vendor Top Sheet". My "active sheet" can be one of many duplicate deal memos that I prepare for my vendors, so it could be one of many different sheets inside the workbook. On all these deal memos, I would like to have a button that executes a macro to do the following:

In "Vendor Top Sheet" on the next available cell in the row specified, I want to insert a formula making it equal to cell B8 of the current active sheet. I haven't been able to figure out how to get a VBA reference to the "Active Sheet" inside the standard excel formula. Here's my code so far:

Sheets("VENDOR TOP SHEET").Range("A38")
                          .End(xlUp)
                          .Offset(0, 11)
                          .Value = FormulaR1C1 
                                 = "='[ " & ActiveSheet.Name & " ]'!B8"

grovesNL grovesNL · Accepted Answer · 2014-11-09T03:41:21

Why are you including the square brackets and spaces here?

Just try using this instead, assuming ActiveSheet is within the same workbook:

Sheets("VENDOR TOP SHEET").Range("A38").End(xlUp).Offset(0, 11).Formula = "='" & ActiveSheet.Name & "'!B8"

Excel VBA - Creating a macro that inserts the active sheet name inside another sheet cell's forumula

2 Answers