PasteSpecial not happening

[Martin Wheeler]

xl2000
The sub SortbyRank is not working for some reason. Instead all I am getting
is the selected range("CE7:CL26") on each worksheet
Should I be doing some sort of "For each sheet" in addition to what I am
already doing?
SortbyRank runs of Test
Any help would be appreciated.
Ta,
Martin

Public Sub SortbyRank(wks As Worksheet)
Application.ScreenUpdating = False
On Error Resume Next
With wks
.Range("K7:R26").Copy
.Range("CE7:CL26").PasteSpecial _
Operation:=xlPasteValues
Application.ScreenUpdating = True
End With
End Sub

Public Sub Test()
Dim wks As Worksheet
For Each wks In ActiveWorkbook.Worksheets
If Left(wks.Name, 1) = "R" Then
With wks
SortbyRank wks
End With
End If
Next
End Sub

 

[Martin Wheeler]

I have just tried adding the "For each Ws in Worksheets" as below but it
still is not working.
I have also tried changing pastevalues to pasteall. That works but is not
what i want as in includes links, formats, etc. I am hoping that
pastevalues will do just that an no more.
Any help would be greatly appreciated.
Ta,
Martin

Public Sub SortbyRank(wks As Worksheet)
Application.ScreenUpdating = False
On Error Resume Next
Dim Ws As Worksheets
With wks
For Each Ws In Worksheets
.Range("K7:R26").Copy
.Range("CE7:CL26").PasteSpecial _
Operation:=xlPasteValues
Next
Application.ScreenUpdating = True
End With
End Sub

 

[Martin Wheeler]

got it, well worked out another way.
Ta,
Martin

I have just tried adding the "For each Ws in Worksheets" as below but it
still is not working.
I have also tried changing pastevalues to pasteall. That works but is not
what i want as in includes links, formats, etc. I am hoping that
pastevalues will do just that an no more.
Any help would be greatly appreciated.
Ta,
Martin

Public Sub SortbyRank(wks As Worksheet)
Application.ScreenUpdating = False
On Error Resume Next
Dim Ws As Worksheets
With wks
For Each Ws In Worksheets
.Range("K7:R26").Copy
.Range("CE7:CL26").PasteSpecial _
Operation:=xlPasteValues
Next
Application.ScreenUpdating = True
End With
End Sub

 

[Don Guillett]

See how great this list is.