Need some help with more effective range manipulation

[havocdragon]

Hi all,

I see alot on here about not using select and active or the like when
editing excel code for efficiency. So I am practicing on revising some code I
have, but I am running into a few problems.

Here is my code, my questions are below it.

Dim testrange As String
Dim SortRangeX As String

testrange = InputBox(prompt:="Enter Column Letter", Title:="Sort")
SortRangeX = testrange & ":" & testrange

Range("4:28").Copy
Sheets("Sort").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlFormulas
Application.CutCopyMode = False
Selection.Sort Key1:=Range(SortRangeX), Order1:=xlDescending
Selection.Copy
Sheets("Main Scorecard (All FSEs)").Select
Range("A4").Select
Selection.PasteSpecial Paste:=xlFormulas
Application.CutCopyMode = False

Now keep in mind, that this is copying from "Main Scorecard(All FSEs)" and
pasting to "Sort" sheet sorting and placing it back into the original sheet.
Here are some issues I have when revising it.

I could do
Range("4:28").Copy
Sheets.("Sort").Range("A1").paste special Paste:=XlFormulas

However the focus is still on the main sheet, and I cannot figure out how to
execute the sort on the sorting sheet without selecting it first.

Also, is there a way to get the range.copy and sheets.range.paste special
all in one line?

 

[Bob Phillips]

Untested, but try this

Dim testrange As String
Dim SortRangeX As String

testrange = InputBox(prompt:="Enter Column Letter", Title:="Sort")
SortRangeX = testrange & ":" & testrange

Range("4:28").Copy
With Sheets("Sort")
.Range("A1").PasteSpecial Paste:=xlFormulas
.Range("A1").Sort Key1:=.Range(SortRangeX), Order1:=xlDescending
.Range("A1").Copy
End With
Sheets("Main Scorecard (All FSEs)").Range("A4").PasteSpecial
Paste:=xlFormulas
Application.CutCopyMode = False


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Dave Peterson]

Is there a reason you don't just sort it on the "main scorecard (all fses)"
sheet directly?

 

[havocdragon]

Bob, I will give that a try,

Dave, I have alot of formating that breaks on the main sheet, so it has to
be done on a seperate sheet =).

 

[havocdragon]

It works to a degree, however

Without the focus on the Sort sheet, the code below doesnt work
..Range("a1").sort

Also, I need it to sort and copy the entire selection I paste into sort
sheet.

 

[Dave Peterson]

Ahhhhh.

Bob, I will give that a try,

Dave, I have alot of formating that breaks on the main sheet, so it has to
be done on a seperate sheet =).

 

[havocdragon]

Here is what I have now, but I still have to have focus on the sort sheet in
order to perform the sort...there has to be a way to sort information without
being on that sheet.

Dim testrange As String
Dim sortrangeX As String
Set ShRng = Worksheets("Sort")

testrange = InputBox(prompt:="Enter Column Letter", Title:="Sort")
sortrangeX = testrange & ":" & testrange

Range("4:28").Copy
ShRng.Range("A1").PasteSpecial Paste:=xlFormulas
Application.CutCopyMode = False
ShRng.Range("Sort_Range").Sort Key1:=Range(sortrangeX),
Order1:=xlDescending
ShRng.Range("Sort_Range").Copy

 

[Dave Peterson]

You can qualify all the ranges you use and never have to activate/select a
sheet. There are only a few things that need to be selected when you're writing
code (freeze panes comes to mind).

I'd approach it this way:

Option Explicit
Sub testme01()

Dim CurWks As Worksheet
Dim TempWks As Worksheet
Dim KeyCol As Range

Set CurWks = ActiveSheet 'change to what you want
CurWks.Select 'easier to point and click at the key col
Set KeyCol = Nothing
On Error Resume Next
'one cell only, too!
Set KeyCol = Application.InputBox(Prompt:="Sort by what column?", _
Type:=8).Cells(1)
On Error GoTo 0

If KeyCol Is Nothing Then
Exit Sub 'user hit cancel
End If

Application.ScreenUpdating = False
Set TempWks = Worksheets.Add 'we'll delete it later
CurWks.Range("4:28").Copy _
Destination:=TempWks.Range("a1")

With TempWks
.UsedRange.Sort key1:=.Columns(KeyCol.Column), order1:=xlDescending, _
header:=xlNo 'change header to match--don't let excel guess!

'clean up anything else in this tempwks (if you have to)

.Rows("1:25").Copy
CurWks.Range("A4").PasteSpecial Paste:=xlPasteFormulas 'xlpastevalues?

Application.DisplayAlerts = False
.Delete
Application.DisplayAlerts = True

End With

Application.ScreenUpdating = True

End Sub

Here is what I have now, but I still have to have focus on the sort sheet in
order to perform the sort...there has to be a way to sort information without
being on that sheet.

Dim testrange As String
Dim sortrangeX As String
Set ShRng = Worksheets("Sort")

testrange = InputBox(prompt:="Enter Column Letter", Title:="Sort")
sortrangeX = testrange & ":" & testrange

Range("4:28").Copy
ShRng.Range("A1").PasteSpecial Paste:=xlFormulas
Application.CutCopyMode = False
ShRng.Range("Sort_Range").Sort Key1:=Range(sortrangeX),
Order1:=xlDescending
ShRng.Range("Sort_Range").Copy