Run Time error '1004': Application-defined or object-defined error

[PBcorn]

I have the following line of code* causing the above error:

sht.activate

*sht.Range(Cells(rwn, 3), Cells(rwn, sht.UsedRange.Columns.Count)).Copy

this is within a for-next loop:

For Each sht In ThisWorkbook.Worksheets
next sht

rwn is an integer from a line within a another for-next loop:

rwn = cll.row

why is this causing an error?

 

[Per Jessen]

Hi

Nothing wrong with the line in question, but I think that the variable rwn
is empty, causing the error.

BTW: Please post the entire code, as it's eaysier to locate the error.

Regards,
Per

 

[PBcorn]

Full code below

the purpose of the macro is to insert a row above certain row labels (in col
2), add a new row label for this row, then add together two particualr
existing rows and paste into the new row. (using pastespecial add operation).
Note that the row labels are repeated once in each sheet.The data looks like:


col2: month1 month2 month3...........................
rowlabel1
rowlabel2
rowlabel3
rowlabel4
rowlabel5
rowlabel1
rowlabel2
rowlabel3
rowlabel4
rowlabel5




Sub tst()

Dim sht As Worksheet
Dim cll As Range
Dim r As Integer
Dim c As Integer
Dim rwn As Integer
Dim rwn2 As Integer
Dim rwn3 As Integer
Dim rwn4 As Integer
Dim rwn5 As Integer
Dim rwn6 As Integer
Dim counter As Integer


'for each "name" extract

For Each sht In ThisWorkbook.Worksheets

If Trim(sht.Range("a1").Value) = "name" Then

'insert new rows just below "CANC"

For Each cll In sht.UsedRange.Columns(2).Cells

If cll.Row >= 2 Then

If Trim(cll.Value) = "DECLINED" And cll.Offset(-1, 0) <> 0 And
cll.Offset(-1, 0).Value <> "CANC+NTU" Then

cll.EntireRow.Insert
cll.Offset(-1, 0).Value = "CANC+NTU"

Else: End If
Else: End If


Next cll

'get row numbers of ranges to be added together

For Each cll In sht.UsedRange.Columns(2).Cells

If cll.Value = "CANC" Or cll.Value = "NTU" Or cll.Value =
"CANC+NTU" Then

counter = counter + 1


If counter = 1 Or counter = 2 Or counter = 3 Then

Select Case Trim(cll.Value)

Case Is = "CANC"
rwn = cll.Row

Case Is = "NTU"
rwn2 = cll.Row

Case Is = "CANC+NTU"
rwn3 = cll.Row

Case Else
End Select

ElseIf counter = 4 Or counter = 5 Or counter = 6 Then

Select Case Trim(cll.Value)

Case Is = "CANC"
rwn4 = cll.Row

Case Is = "NTU"
rwn5 = cll.Row

Case Is = "CANC+NTU"
rwn6 = cll.Row

Case Else
End Select

Else: End If
Else: End If

Next cll

' add rows into new row

'm

c = sht.UsedRange.Columns.Count

MsgBox c
MsgBox sht.Range(Cells(rwn, 3), Cells(rwn, c)).Address
MsgBox c

sht.Range(Cells(rwn, 3), Cells(rwn, c)).Copy
sht.Range(Cells(rwn3, 3), Cells(rwn3, c)).Paste


sht.Range(Cells(rwn2, 3), Cells(rwn2, c)).Copy
sht.Range(Cells(rwn3, 3), Cells(rwn3, c)).PasteSpecial operation:=xlAdd

'p

sht.Range(Cells(rwn4, 3), Cells(rwn4, c)).Copy
sht.Range(Cells(rwn6, 3), Cells(rwn6, c)).Paste


sht.Range(Cells(rwn5, 3), Cells(rwn5, c)).Copy
sht.Range(Cells(rwn6, 3), Cells(rwn6, c)).PasteSpecial operation:=xlAdd


Else: End If

Next sht

End Sub

 

[Dave Peterson]

Qualify the ranges:

sht.Range(sht.Cells(rwn, 3), sht.Cells(rwn, sht.UsedRange.Columns.Count)).Copy

or
with sht
'notice the leading dots!
.Range(.Cells(rwn, 3), .Cells(rwn, .UsedRange.Columns.Count)).Copy
end with

If the code is behind a worksheet, those unqualified ranges refer to the sheet
with the code.

If the code is in a general module, then those unqualified ranges refer to the
activesheet.

 

[PBcorn]

I just noticed, some trim() functions were missing, causing rwn to be 0. Now
however I am getting "object does not support this property or method" for
the paste line.

 

[PBcorn]

I have qualified these ranges but still get "object does not support this
property or method" on the paste line:

sht.Range(sht.Cells(rwn3, 3), sht.Cells(rwn3, c)).Paste

I have checked the paste range, it is the same size as the copied range.

 

[PBcorn]

problem sorted - I replaced .Paste with .Pastespecial. VB Help implies that
the paste method does not apply to the range object, but Pastespecial does.
Strange.

 

[Dave Peterson]

How about:

sht.Range(sht.Cells(rwn, 3), sht.Cells(rwn, sht.UsedRange.Columns.Count)).Copy _
destination:=sht.Cells(rwn3, 3)