CODEWITHSUNDEEP
vbaexcel
Brandon
Brandon

Reputation: 75

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

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"

Upvotes: 0

Views: 1000

Answers (2)

Abe Gold
Abe Gold

Reputation: 2357

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

Upvotes: 1

grovesNL
grovesNL

Reputation: 6075

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"

Upvotes: 1

Related Questions