Juriemagic
09-30-2015, 01:38 AM
Hi good people!,
I am having trouble with the following code. It keeps giving an error stating "Paste special method of Range Class failed"...The part that highlites yellow is this part:
wks2.Cells(lr2, "A").PasteSpecial xlValues.
The full code is:
Application.ScreenUpdating = False
Dim i As Long
Dim lr1 As Long, lr2 As Long
Dim Delta As String
Dim wks1 As Worksheet, wks2 As Worksheet
Set wks1 = Worksheets("Active Work Orders")
Set wks2 = Worksheets("Completed Work Orders")
If Range("AG4") = 1 Then
Range("AG4").Value = 0
lr1 = wks1.Cells(Rows.Count, "U").End(xlUp).Row
For i = 2 To lr1
Delta = wks1.Cells(i, "U").Value
If Delta = Range("E8").Value Then
lr2 = wks2.Cells(Rows.Count, "T").End(xlUp).Row + 1
wks1.Cells(i, "A").Resize(, 36).Copy
wks2.Activate
wks2.Unprotect
wks2.Cells(lr2, "A").PasteSpecial xlValues
wks1.Unprotect
wks1.Cells(i, "A").Resize(, 35).ClearContents
End If
Next i
Range("T28:AJ100").Select
ActiveWorkbook.Worksheets("ACTIVE WORK ORDERS").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("ACTIVE WORK ORDERS").Sort.SortFields.Add Key:= _
Range("T28"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("ACTIVE WORK ORDERS").Sort
.SetRange Range("T28:AJ100")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
wks1.Activate
wks1.Range("K22").Value = ""
Range("AG4").Value = 0
wks1.Range("E8").Select
MsgBox "Workorder had been archived", vbInformation
Else
GoTo a:
a:
Exit Sub
End If
End Sub
Any and all help will be greatly appreciated!..Thank you all kindly...
NOTE:..I see for some reason the CODE tags don't do what they are supposed to do...
I am having trouble with the following code. It keeps giving an error stating "Paste special method of Range Class failed"...The part that highlites yellow is this part:
wks2.Cells(lr2, "A").PasteSpecial xlValues.
The full code is:
Application.ScreenUpdating = False
Dim i As Long
Dim lr1 As Long, lr2 As Long
Dim Delta As String
Dim wks1 As Worksheet, wks2 As Worksheet
Set wks1 = Worksheets("Active Work Orders")
Set wks2 = Worksheets("Completed Work Orders")
If Range("AG4") = 1 Then
Range("AG4").Value = 0
lr1 = wks1.Cells(Rows.Count, "U").End(xlUp).Row
For i = 2 To lr1
Delta = wks1.Cells(i, "U").Value
If Delta = Range("E8").Value Then
lr2 = wks2.Cells(Rows.Count, "T").End(xlUp).Row + 1
wks1.Cells(i, "A").Resize(, 36).Copy
wks2.Activate
wks2.Unprotect
wks2.Cells(lr2, "A").PasteSpecial xlValues
wks1.Unprotect
wks1.Cells(i, "A").Resize(, 35).ClearContents
End If
Next i
Range("T28:AJ100").Select
ActiveWorkbook.Worksheets("ACTIVE WORK ORDERS").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("ACTIVE WORK ORDERS").Sort.SortFields.Add Key:= _
Range("T28"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("ACTIVE WORK ORDERS").Sort
.SetRange Range("T28:AJ100")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
wks1.Activate
wks1.Range("K22").Value = ""
Range("AG4").Value = 0
wks1.Range("E8").Select
MsgBox "Workorder had been archived", vbInformation
Else
GoTo a:
a:
Exit Sub
End If
End Sub
Any and all help will be greatly appreciated!..Thank you all kindly...
NOTE:..I see for some reason the CODE tags don't do what they are supposed to do...