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?

"Jim Rech" wrote:

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
"PBcorn" wrote in message
...
| 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
|