P
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
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