Targeting Worksheet Name by Cell Value

[daffodil11]

I have some a column of numbers "A" and I have tabs that are named after them.

How do I add each sheets A1 total to the current value of Column B?

It seemed simple, but I just can't debug this at all; I'm getting a subscript out of range error when trying to target Sheets(c).Range("A1"). c.Value didn't work either.

Please Login or Register  to view this content.

[Leith Ross]

Hello daffodil1,

You are receiving the error message because you don't have 10 sheets in the workbook.
Can you post before and after examples or even the workbook?

[daffodil11]

My actual range and offset are a little different.

I'm mostly interested in how to take the offset of each value in a range, and add to it the value from a sheet named after each cell in the starting range.

(And without the use of regular circular referencing formulas.)

Please Login or Register  to view this content.

[Leith Ross]

Hello daffodil11,

You can use this formula and drag it down...

Please Login or Register  to view this content.

[daffodil11]

This is for the updating of a form, which can't have a circular reference or a helper column.

I'm specifically looking for a VB solution to update a column of totals in K, where the values of A are the Sheet Names. Would you recommend I remove the For and simply target each with their own code block?

If Range("A1") <> "" Then Range("K1").Value = Range("K1") + Sheets(Range("A1").Value).Range("A1")
If Range("A2") <> "" Then Range("K2").Value = Range("K2") + Sheets(Range("A2").Value).Range("A1")
If Range("A3") <> "" Then Range("K3").Value = Range("K3") + Sheets(Range("A3").Value).Range("A1")
ETC

[Leith Ross]

Hello daffodil11,,

Sorry for the delay, I got a couple of phone calls. To do this VBA...

Please Login or Register  to view this content.

[daffodil11]

So awesome. I've been working on a variation of this for our forecasting updates.

Much appreciated.

[Leith Ross]

Hello daffodil11,

'S e do bheatha. (You're welcome)

[daffodil11]

Gesundheit!