need help with nested for-next loops
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
|