VBA Formula and other worksheets

[Ezguy4u]

The workbook contains 3 worksheets. They are labeled Data 1, 2 and 3. Data 1 worksheet has that formula generated by VBA. So based on the below example, the question I have is there any other way to reference the 2 worksheets Data 2 and Data 3. I want to reference those 2 worksheets dynamically. If I add a worksheet or delete a worksheet, I would like the program to adjust for this. I hope this is clear enough. If not let me know.

Sub Formula1()
Range("A1").Select
    ActiveCell.FormulaR1C1 = "=SUM('Data 2:Data 3'!RC)"
    Range("A2").Select
End Sub

Formula Worksheet.xlsm
	A
1	12
Data 1

Cell Formulas
Range	Formula
A1	A1	=SUM('Data 2:Data 3'!A1)

 

[Peter_SSs]

My suggestion would be to make a new sheet (called say 'Data 0') and position it at the left of 'Data 2' and also a new sheet (called say 'Data n') and position it just to the right of 'Data 3' and

- Use this instead (if you really need vba to add it?)

Sub Formula1_v2()
  Sheets("Data 1").Range("A1").FormulaR1C1 = "=SUM('Data 0:Data n'!RC)"
End Sub

- Make sure that any new sheets that you want included in the sum are added between 'Data 0' and 'Data n' and then the formula will never need adjusting for the new sheets added (or deleted)

(If you want, for visual effect, you can hide 'Data 0' and 'Data n', but you would need to remember that they are there and ensure that new sheets are added between them still)

 

[Ezguy4u]

Well, this would work and I am thinking about how I can set up my workbook to make it work but I was wondering if there are other alternatives.

 

[Peter_SSs]

I was wondering if there are other alternatives.

Assuming that 'Data 1', where the formula is going, is the first sheet in the workbook and that there will always be at least one other worksheet, see if this - put into the 'Data 1' sheet's module does what you want.

Private Sub Worksheet_Activate()
  Range("A1").FormulaR1C1 = "=SUM('" & Sheets(2).Name & ":" & Sheets(Sheets.Count).Name & "'!RC)"
End Sub

 

[Ezguy4u]

Peter this works the best. This is exactly what I was looking for. I can name Data 1 sheet01 and it should be the first one. I like the fact I can name a worksheet anything and the program doesn't care. And there will always be more than 1 worksheet. This is the syntax I was looking for. Thank You.

 

[Peter_SSs]

You're welcome. Thanks for the confirmation.