Drag formula across with source from sheets

[mkess]

It is the first time I post here. I've done a quick search on my question and haven't found anything...
So my question is:

On Excel, say I have data on ten years of international trade, organized such as each tab is one year.

I want to create in a new tab a simple table with the total trade =sum('2000'$AA$2:$AA$27) for 2000 in the first cell, =sum('2001'$AA$2:$AA$27) in the second cell, etc.

My question is : is there a way to do this without manually changing the sheet of reference each time I want the sum for the next year? Drag the formula so that each cell links to a different tab (but the same cell within each tab)...

Is the question clear?

 

[kevin lazell]

hello mkess
welcome to the board
try the following code i think it will do as you want.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim z As Variant
Dim y As Variant
Dim ws As Worksheet
y = 1
For Each ws In ThisWorkbook.Worksheets
With ws
z = Application.WorksheetFunction.Sum(.Range("aa2:aa27"))

Range("a" & y) = z
y = y + 1
If y = 11 Then Exit Sub
End With
Next ws
End Sub

create a new sheet and right click on the sheet tab and select view code
and paste the code in the box that opens up

cheers

kevin

 

[mkess]

Thanks a lot - only problem is, I have no idea how to use this, I do not know how to program under Excel.
Can you point me to a thread/website where I can learn how to make use of your solution without investing too much time?

hello mkess
welcome to the board
try the following code i think it will do as you want.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim z As Variant
Dim y As Variant
Dim ws As Worksheet
y = 1
For Each ws In ThisWorkbook.Worksheets
With ws
z = Application.WorksheetFunction.Sum(.Range("aa2:aa27"))

Range("a" & y) = z
y = y + 1
If y = 11 Then Exit Sub
End With
Next ws
End Sub

create a new sheet and right click on the sheet tab and select view code
and paste the code in the box that opens up

cheers

kevin

 

[kevin lazell]

hello mkess

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim z As Variant
Dim y As Variant
Dim ws As Worksheet
y = 1
For Each ws In ThisWorkbook.Worksheets
With ws
z = Application.WorksheetFunction.Sum(.Range("aa2:aa27"))

Range("a" & y) = z
y = y + 1
If y = 11 Then Exit Sub
End With
Next ws
End Sub

to copy the above code left click just before private hold button down and drag across until all of the line has turned blue
then drag down to end sub and then release the left button. then right click and in the box that opens select copy.
create a new sheet and right click on the sheet tab and select view code
and paste the code in the box that opens up
close the box and then click anywhere in the spreadsheet you created and the code will do its stuff

cheers

kevin