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

J

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
 
J

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
 
J

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.
 
J

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top