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

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

Run-time error '1004': Method 'Range' of object '_Worksheet' faile
 

Worked for me in xl2003.

" wrote:

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

Run-time error '1004': Method 'Range' of object '_Worksheet'faile
 

On Mar 6, 1:48*pm, JLGWhiz wrote:
Worked for me in xl2003.



" wrote:
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- Hide quoted text -

- 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

Run-time error '1004': Method 'Range' of object '_Worksheet' f
 

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.

" wrote:

On Mar 6, 1:48 pm, JLGWhiz wrote:
Worked for me in xl2003.



" wrote:
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- Hide quoted text -

- 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

Run-time error '1004': Method 'Range' of object '_Worksheet' f
 

On Mar 6, 2:26*pm, JLGWhiz wrote:
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.



" wrote:
On Mar 6, 1:48 pm, JLGWhiz wrote:
Worked for me in xl2003.

" wrote:
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- Hide quoted text -

- 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- Hide quoted text -

- 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