Run-time error '1004': Method 'Range' of object '_Worksheet'failed... again!

[john.mctigue]

A similar problem to my previous post - this time however the code
fails on the line beneath the 'Copy disease group comment. I'm
providing the whole sub for context. I am copying data from a source
workbook wherein each worksheet relates to an particular disease
group, and within each sheet there are identically spaced ranges (one
for each health region) containing the data of interest.

I am curious as to why this syntax is failing as it would seem to be
identical to that in the Excel 2003 help file under the 'Range
Property' topic, namely:
This example sets the font style in cells A1:C5 on Sheet1 to italic.
The example uses Syntax 2 of the Range property.

Worksheets("Sheet1").Range(Cells(1, 1), Cells(5, 3)). _
Font.Italic = True

===========================================
Sub ExportDataRanges()

Dim SourceSheet As Worksheet
Dim TargetSheet As Worksheet
Dim i As Integer

Set TargetSheet = Application.Workbooks("Summary.xls").Worksheets
("Sheet3")
'Add column headings
TargetSheet.Cells(1, 1).Value = "Disease Group"
TargetSheet.Cells(1, 2).Value = "Health Region"
TargetSheet.Cells(1, 3).Value = "Sex"
TargetSheet.Cells(1, 4).Value = "Year"
TargetSheet.Cells(1, 5).Value = "ASR"
TargetSheet.Cells(1, 6).Value = "ASR LCI"
TargetSheet.Cells(1, 7).Value = "ASR UCI"
TargetSheet.Cells(1, 8).Value = "Cases"
TargetSheet.Cells(1, 9).Value = "Cases LCI"
TargetSheet.Cells(1, 10).Value = "Cases UCI"
'Fill column headings (tan colour)
With TargetSheet.Range(Cells(1, 1), Cells(1, 10)).Interior
.ColorIndex = 40
.Pattern = xlSolid
End With

For Each SourceSheet In Workbooks("T090035N Males.xls").Worksheets
For i = 0 To 11
'Copy disease group
TargetSheet.Range(Cells((i * 39) + 2, 1), Cells((i * 39) + 40,
1)).Value = SourceSheet.Range(Cells((i * 44) + 4, 1)).Value
'Copy health region
TargetSheet.Range(Cells((i * 39) + 2, 2), Cells((i * 39) + 40,
1)).Value = SourceSheet.Range(Cells((i * 44) + 2, 1)).Value
'Gender is male for this workbook
TargetSheet.Range(Cells((i * 39) + 2, 3), Cells((i * 39) + 40,
1)).Value = "Male"
'Copy source data for single health region for years 1998 to
2036
TargetSheet.Range(Cells((i * 39) + 2, 4), Cells((i * 39) + 40,
10)).Value = SourceSheet.Range(Cells((i * 44) + 4, 25), Cells((i * 44)
+ 42, 31)).Value
Next i
Next SourceSheet

Set TargetSheet = Nothing

End Sub

 

[JLGWhiz]

Worked for me in xl2003.

 

[john.mctigue]

Worked for me in xl2003.













- Show quoted text -

Thanks JLGWhiz,

I am still having no joy and am feeling a deep sense of melancholy,
and I would be grateful for any ideas.

Kind regards,
John

 

[JLGWhiz]

Since you do not appear to be using variables for rows or columns, why don't
you just use the A1 format for the range reference. The cells reference
sometimes takes a mind of its own since it is really not a range object class.

 

[john.mctigue]

Since you do not appear to be using variables for rows or columns, why don't
you just use the A1 format for the range reference.  The cells reference
sometimes takes a mind of its own since it is really not a range object class.








- Show quoted text -

Many thanks JLGWhiz,

That seems to have done the trick. I just thought the Cells method
was rather elegant, to my convoluted way of thinking.

Kind regards,
John