CODEWITHSUNDEEP
excelvbaexcel-formula
Shura
Shura

Reputation: 1

VBA formula referencing a changing sheet name

I am new to VBA and am trying to create a code that uses the formula which references a sheet. However, the sheet name changes every month. What functions should I be using to reference the sheet?

I have thought of creating a simple macro to rename the sheet in question as "Raw data" to be easily referenced in the formula code but I would wish to avoid doing so. This is the code so far:

Range("J2").Formula = "=SUMPRODUCT(--ISNUMBER(SEARCH(Checklist!R2C1:R6C1,WorkSheets(1)!RC[-1])))>0"

Worksheets(1) is the constantly changing sheet name that I wish to reference in the code. I thought of using Worksheets(1) to reference the left most worksheet since that does not require the sheet's name. J2 is the cell in the sheet which constantly changes name.

Upvotes: 0

Views: 1067

Answers (1)

Shura
Shura

Reputation: 1

I have modified the code to ,'" & ActiveWorkbook.Sheets(1).Name & "'! and it is working as intended now. Thanks for the help!!

Upvotes: 0

Related Questions