method "range" of object "_worksheet" fails

[PBcorn]

line ** generates the above error. Please advise.


code:
Sub clean()

Dim mtsumc As Range
Dim mtsumn As Range
Dim psumc As Range
Dim psumn As Range


Dim curcell As Range
Dim sht As Worksheet
Dim el1 As Integer
Dim el2 As Integer
Dim c As Integer

Dim P, M As Boolean


For Each sht In ActiveWorkbook.Worksheets

c = sht.UsedRange.Columns.Count

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

el1 = curcell.Row

Select Case Trim(curcell.Offset(0, -1).Value)

Case Is = "Motor"

M = True

Select Case Trim(curcell.Value)

Case Is = "A"
Set mtsumc = sht.Range(Cells(el1, 3), Cells(el1, c))

Case Is = "B"
Set mtsumn = sht.Range(Cells(el1, 3), Cells(el1, c))

Case Is = "A+B"
MsgBox "file already processed"
Exit Sub

End Select

Case Is = "Property"

P = True

Select Case Trim(curcell.Value)

Case Is = "A"
Set psumc = sht.Range(Cells(el1, 3), Cells(el1, c))

Case Is = "B"
Set psumn = sht.Range(Cells(el1, 3), Cells(el1, c))

Case Is = "A+B"
MsgBox "file already processed"
Exit Sub

End Select

End Select
Next curcell



If M = True Then
With sht
..Range("mtsumn").Copy**
..Range("mtsumc").PasteSpecial operation:=xlAdd
End With
Application.CutCopyMode = False
Else: End If

If P = True Then
With sht
..Range("psumn").Copy
..Range("psumc").PasteSpecial operation:=xlAdd
End With
Application.CutCopyMode = False
Else: End If




Next sht
End Sub

 

[Daniel.C]

Is "mtsumn" named range defined to the worksheet level or to the
workbook level ? In the second case, you should not use it with a
worksheet.
Regards.
Daniel

 

[PBcorn]

my intention is for all the named ranges to be set and used at the sheet
level. What code changes are needed to make this work?

 

[Jim Rech]

With sht
..Range("mtsumn").Copy**

If any sheet does not have this local name defined on it you'll get an error
when the macro gets to that sheet.

--
Jim
| line ** generates the above error. Please advise.
|
|
| code:
| Sub clean()
|
| Dim mtsumc As Range
| Dim mtsumn As Range
| Dim psumc As Range
| Dim psumn As Range
|
|
| Dim curcell As Range
| Dim sht As Worksheet
| Dim el1 As Integer
| Dim el2 As Integer
| Dim c As Integer
|
| Dim P, M As Boolean
|
|
| For Each sht In ActiveWorkbook.Worksheets
|
| c = sht.UsedRange.Columns.Count
|
| For Each curcell In sht.UsedRange.Columns(2).Cells
|
| el1 = curcell.Row
|
| Select Case Trim(curcell.Offset(0, -1).Value)
|
| Case Is = "Motor"
|
| M = True
|
| Select Case Trim(curcell.Value)
|
| Case Is = "A"
| Set mtsumc = sht.Range(Cells(el1, 3), Cells(el1, c))
|
| Case Is = "B"
| Set mtsumn = sht.Range(Cells(el1, 3), Cells(el1, c))
|
| Case Is = "A+B"
| MsgBox "file already processed"
| Exit Sub
|
| End Select
|
| Case Is = "Property"
|
| P = True
|
| Select Case Trim(curcell.Value)
|
| Case Is = "A"
| Set psumc = sht.Range(Cells(el1, 3), Cells(el1, c))
|
| Case Is = "B"
| Set psumn = sht.Range(Cells(el1, 3), Cells(el1, c))
|
| Case Is = "A+B"
| MsgBox "file already processed"
| Exit Sub
|
| End Select
|
| End Select
| Next curcell
|
|
|
| If M = True Then
| With sht
| .Range("mtsumn").Copy**
| .Range("mtsumc").PasteSpecial operation:=xlAdd
| End With
| Application.CutCopyMode = False
| Else: End If
|
| If P = True Then
| With sht
| .Range("psumn").Copy
| .Range("psumc").PasteSpecial operation:=xlAdd
| End With
| Application.CutCopyMode = False
| Else: End If
|
|
|
|
| Next sht
| End Sub
|

 

[Daniel.C]

Could you post the formula which defines "mtsumn" ?
Daniel

 

[PBcorn]

ok what am trying to acheive is have the macro cycle through all the sheets
in the workbook and assign new ranges to the named ranges for each sheet,
then use this for operations within the sheet. How can this be done?