TSmith
12-16-2008, 09:15 AM
I have a series of worksheets in a single file. Below is the code attached to a command button on the worksheet 'Trip Cards 1' to search through this worksheet and based on the value in the searched range, copy the value from a selected cell and paste it to the next open row on another worksheet (Cancellations). Since the worksheet to paste to will be cleared each time this runs (I did not put this code in yet), I have set the LastCell variable to the first paste to cell.
When I run this I get the Method 'range' of object '_Worksheet' failed on the line marked below. Any ideas???
Private Sub CommandButton2_Click()
'code to generate cancellation log
'create temporary range objects for union
Sheets("Trip Cards 1").Activate
'turn off updating
Application.ScreenUpdating = False
'unprotect the sheet
ActiveSheet.Unprotect Password:=""
'define variables
Dim r1 As Range
Dim r2 As Range
Dim r3 As Range
Dim r4 As Range
Dim CopyPos As Range
Dim CurrPos As Range
Dim DestPos As Range
Dim LastCell As Range
'create union range object
Dim rU As Range
'define cell object within the range
Dim r As Range
'set temporary range objects to match named ranges
'multiple named ranges used due to 255 char limit on range names
Set r1 = Range("TripNum2")
Set r2 = Range("TripNum3")
Set r3 = Range("TripNum4")
Set r4 = Range("TripNum5")
'combine the ranges into one master range
Set rU = Union(r1, r2, r3, r4)
Sheets("Cancellations").Activate
Set LastCell = Sheets("Cancellations").Range("A6")
Sheets("Trip Cards 1").Activate
'loop through the master range and find the cancellation values
For Each r In rU.Areas
If r.Value = "C" Then
Set CurrPos = r
CurrPos.Offset(0, 3).Select
Selection.Copy
Sheets("Cancellations").Select
'With ActiveSheet
Range("LastCell").Select 'FAILS HERE
ActiveSheet.Paste
LastCell = LastCell.Offset(1, 0)
'End With
Sheets("Trip Cards 1").Activate
End If
Next r
Application.ScreenUpdating = True
ActiveSheet.Protect Password:=""
End Sub
Tim
:banghead:
When I run this I get the Method 'range' of object '_Worksheet' failed on the line marked below. Any ideas???
Private Sub CommandButton2_Click()
'code to generate cancellation log
'create temporary range objects for union
Sheets("Trip Cards 1").Activate
'turn off updating
Application.ScreenUpdating = False
'unprotect the sheet
ActiveSheet.Unprotect Password:=""
'define variables
Dim r1 As Range
Dim r2 As Range
Dim r3 As Range
Dim r4 As Range
Dim CopyPos As Range
Dim CurrPos As Range
Dim DestPos As Range
Dim LastCell As Range
'create union range object
Dim rU As Range
'define cell object within the range
Dim r As Range
'set temporary range objects to match named ranges
'multiple named ranges used due to 255 char limit on range names
Set r1 = Range("TripNum2")
Set r2 = Range("TripNum3")
Set r3 = Range("TripNum4")
Set r4 = Range("TripNum5")
'combine the ranges into one master range
Set rU = Union(r1, r2, r3, r4)
Sheets("Cancellations").Activate
Set LastCell = Sheets("Cancellations").Range("A6")
Sheets("Trip Cards 1").Activate
'loop through the master range and find the cancellation values
For Each r In rU.Areas
If r.Value = "C" Then
Set CurrPos = r
CurrPos.Offset(0, 3).Select
Selection.Copy
Sheets("Cancellations").Select
'With ActiveSheet
Range("LastCell").Select 'FAILS HERE
ActiveSheet.Paste
LastCell = LastCell.Offset(1, 0)
'End With
Sheets("Trip Cards 1").Activate
End If
Next r
Application.ScreenUpdating = True
ActiveSheet.Protect Password:=""
End Sub
Tim
:banghead: