EXCEL HELP

Closed
Report
Posts
3
Registration date
Sunday July 7, 2013
Status
Member
Last seen
July 17, 2013
-
Posts
2847
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 13, 2022
-
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

1 reply

Posts
2847
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 13, 2022
491
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
0