EXCEL HELPClosed

Question

Hello,

Novice needs help 

I am trying to extract the contents of cells from several worksheets and summarize on one Header Summary Sheet

So i'm trying to generate on the Header Summary Sheet the contents of

Sheet2014001 cell B7
Sheet2014002 cell B7
Sheet2014003 cell B7
Sheet2014004 cell b7
and so on

I thought if i just went
=Sheet2014001$B$7
=Sheet2014002$B$7
=Sheet2014003$B$7

and then tried to drag the formulae down i would get
= Sheet2014004$B$7
=Sheet2014005$B$7
and the appropriate cell contents

but instead i get
=Sheet2014001$B$7
and the cell contents from that Sheet


Anyone have any idea how i can easily achieve what i want?

Any assistance would be greatly appreciated and thanks in advance

TrowaD · Answer

Hi Paul,

I think the easiest way is to use a code:
Sub Test()
Dim SheetRef, x As Long, rCount As Integer

x = InputBox("Starting from sheet 2014001 to which sheet reference would you like to go?:")

SheetRef = 2014001 - 1
rCount = 0

Do
    rCount = rCount + 1
    SheetRef = SheetRef + 1
    Cells(rCount, "A").Formula = "=Sheet" & SheetRef & "!B7"
Loop Until SheetRef = x

End Sub
Best regards,
Trowa

EXCEL HELP

1 response

Similar discussions

Subscribe To Our Newsletter!