I made a small change and now it works. Sorry I didn't look carefully at all
the data when I originally tested the code.

the following statement reads 52 values from the old worksheet skipping
cells as requested
Set OldRange = Sheets(ws.Name). _
Range("H5:H17,H20:H32,H36:H48,H52:H64")

The code takes 52 weeks of data in one worksheet and puts it in an array
SumArray. Then goes to next worksheet and add the 52 weeks of data to the
SumAray. The macro keeps doing this for all worksheets. Then the macro
write the final array back in the new worksheet.


Sub getolddata()

Const OldBook = "c:\temp\Old Workbook.xls"

Workbooks.Open Filename:=OldBook
OldBkName = ActiveWorkbook.Name

Dim SumArray(52)
For MyWeek = 1 To 52
SumArray(MyWeek) = 0
Next MyWeek

For Each ws In Worksheets

Set OldRange = Sheets(ws.Name). _
Range("H5:H17,H20:H32,H36:H48,H52:H64")

MyWeek = 1
For Each cell In OldRange
SumArray(MyWeek) = SumArray(MyWeek) + _
cell.Value
MyWeek = MyWeek + 1
Next cell
Next ws

For MyWeek = 1 To 52
ThisWorkbook.Sheets("Sheet1"). _
Range("B6").Offset(0, MyWeek - 1) = _
SumArray(MyWeek)
Next MyWeek

Workbooks(OldBkName).Close
End Sub

"rpw" wrote:

Correction: It works, but not like I thought it would. The cell range being
summed is not skipping from H17 to H20 nor from H32 to H36.
--
rpw


"Joel" wrote:

Sub getolddata()

Const OldBook = "c:\temp\Old Workbook.xls"

Workbooks.Open Filename:=OldBook
OldBkName = ActiveWorkbook.Name

Dim SumArray(52)
For Myweek = 1 To 52
SumArray(Myweek) = 0
Next Myweek

For Each ws In Worksheets

Set OldRange = Sheets(ws.Name). _
Range("H5:H17,H20:H32,H36:H48")

For Myweek = 1 To 52
SumArray(Myweek) = SumArray(Myweek) + _
OldRange(Myweek)
Next Myweek
Next ws

For Myweek = 1 To 52
ThisWorkbook.Sheets("Sheet1"). _
Range("B6").Offset(0, Myweek - 1) = _
SumArray(Myweek)
Next Myweek

Workbooks(OldBkName).Close
End Sub



"rpw" wrote:

Hi all,

I have a old workbook with several worksheets. Each sheet is formatted the
same having weekly entries grouped quarterly for a 1 year period. (Each row
is 1 week).

I have a new workbook with each week in columns.

I need to have the sum of all week 1 (cell "H5") entries from every
worksheet in the old workbook put into the week 1 cell "B6" in the new
workbook.

And then all week 2 (cell "H6") into week 2 cell "C6" and so on.

The data in the old workbook is in four ranges (H5:H17, H20:H32, H36:H48,
H52:H64) on each worksheet. The weekly sums go into the new workbook range
of "B6:BA6".

So, how do I do this?

Thanks in advance for any guidance provided

rpw