Dynamically reference different sheets in vba

Question

I am trying to reference different sheets in a macro without explicitly referencing them so as to make the macro compatible with any range of sheets. I have happily been using (ActiveSheet.Index +/- 1) for switching between sheets, but when it comes to entering formulae, I can't find anything to help me so far.

The route I have gone down thus far is using a macro I found at: http://www.cpearson.com/excel/sheetref.htm

which references the name of the next sheet when entered in a cell. e.g. =NextSheetName() returns 'Sheet 2'.

I combine that result with an ! mark and define it as nexxxtsheet.

So when I enter the Formula

ActiveCell.FormulaR1C1 = "=" & nexxxtsheet & " "A1"

I expect the cell to have the formula: ='Name of next sheet'!A1

However it returns ='Name of next sheet'!'A1'

The difference being the quotes around A1 - rendering the formula useless. I am rather new to VBA and have no idea why this happens, and I am entirely certain that the method I am using to switch sheets without a direct reference is, for lack of a better term, rather crackhouse-style. I am very open to suggestions of how I should better switch between sheets, or corrections to render my formula useful.

Thanks all!

Define Dim nexxxtsheet As String , the set it nexxxtsheet = "Sheet2" (for instance), and the use the Formula (not FormulaR1C1) like : ActiveCell.Formula = "=" & nexxxtsheet & "!" & "A1" . Keep in mind, it is better to avoid using ActiveCell — Shai Rado
– Shai Rado, Commented Oct 10, 2016 at 8:53
Thank you very much for your help - as I am new I was wondering if you could explain what the difference between formula and Formula R1C1 was? — Lukas Ljungstrom
– Lukas Ljungstrom, Commented Oct 10, 2016 at 9:07
@LukasLjungstrom R1C1 is another way of referencing cells (by their Row and Column number). For example R2C3 is the cell usually known as C2. .FormulaR1C1 is used to set the formula with these references (excel will convert them afterwards) so it doesn't understand A1 as a cell reference. If you search for R1C1 reference style you will find lots of information. — arcadeprecinct
– arcadeprecinct, Commented Oct 10, 2016 at 9:51

Pierre · Accepted Answer · 2016-10-10 08:54:46Z

1

with "formula" , not "formulaR1C1", it works for me. Except if cell format is text, in which case you need to change it first

answered Oct 10, 2016 at 8:54

Pierre

1,0467 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Dynamically reference different sheets in vba

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related