Don't know what to do

[colinheslop1984]

I have 5 documents with 52 tabs, one per week.

I have another sheet where I want to consolidate the data.

Because the sheets represent week numbers I want to throw in a criteria referencing the sheet name so that I can just copy and paste the formula into the weekly tabs of my master sheet rather than having to retype them all.

For example if I have sheets 1:52 on all documents including master and I use

=INDEX('[LE2.xlsx]22'!$D$5:$D$6,1,1)

this will sum exactly what I need but only for this particular worksheet so it means I would have to change 22! to 23! to 24! for every calculation I need to make which would take too long.

What if I have the worksheet name '22' or '23' etc in cell I1, how can I use this to capture the data I need.

In other words, I want to search all worksheets for [LE2] for the one which matches the name in I1 then sum cell D5 from that tab. How do I do it?

I was thinking indirect but then I'm not sure how to write this up when its referencing an external source

 

[colinheslop1984]

Anyone??

I have 5 documents with 52 tabs, one per week.

I have another sheet where I want to consolidate the data.

Because the sheets represent week numbers I want to throw in a criteria referencing the sheet name so that I can just copy and paste the formula into the weekly tabs of my master sheet rather than having to retype them all.

For example if I have sheets 1:52 on all documents including master and I use

=INDEX('[LE2.xlsx]22'!$D$5:$D$6,1,1)

this will sum exactly what I need but only for this particular worksheet so it means I would have to change 22! to 23! to 24! for every calculation I need to make which would take too long.

What if I have the worksheet name '22' or '23' etc in cell I1, how can I use this to capture the data I need.

In other words, I want to search all worksheets for [LE2] for the one which matches the name in I1 then sum cell D5 from that tab. How do I do it?

I was thinking indirect but then I'm not sure how to write this up when its referencing an external source

 

[Logit]

.

Option Explicit


Sub foo()
Dim ws As Worksheet


For Each ws In ActiveWorkbook.Worksheets
    ws.Range("D7").Formula = "=(A1)"    '<-- your formula here
Next


End Sub

 

[colinheslop1984]

I dont understand

 

[Logit]

.
The macro I posted should be inserted into a regular Module. You activate it by a command button placed on your worksheet.

Where you see "=(A1)" ... replace that portion with your formula ... "=INDEX('[LE2.xlsx]22'!$D$5:$D$6,1,1)" . Be certain to place your formula inside the quotes.

Because I don't have your workbook/s here to test with .... I am not certain if your formula will function as desired or if you will need to edit your formula.