|
Need some help with more effective range manipulation
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? |
Need some help with more effective range manipulation
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) "havocdragon" wrote in message ... 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? |
Need some help with more effective range manipulation
Is there a reason you don't just sort it on the "main scorecard (all fses)"
sheet directly? havocdragon wrote: 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? -- Dave Peterson |
Need some help with more effective range manipulation
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 =). "Dave Peterson" wrote: Is there a reason you don't just sort it on the "main scorecard (all fses)" sheet directly? havocdragon wrote: 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? -- Dave Peterson |
Need some help with more effective range manipulation
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. "Bob Phillips" wrote: 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) "havocdragon" wrote in message ... 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? |
Need some help with more effective range manipulation
Ahhhhh.
havocdragon wrote: 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 =). "Dave Peterson" wrote: Is there a reason you don't just sort it on the "main scorecard (all fses)" sheet directly? havocdragon wrote: 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? -- Dave Peterson -- Dave Peterson |
Need some help with more effective range manipulation
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" wrote: Ahhhhh. havocdragon wrote: 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 =). "Dave Peterson" wrote: Is there a reason you don't just sort it on the "main scorecard (all fses)" sheet directly? havocdragon wrote: 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? -- Dave Peterson -- Dave Peterson |
Need some help with more effective range manipulation
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 havocdragon wrote: 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" wrote: Ahhhhh. havocdragon wrote: 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 =). "Dave Peterson" wrote: Is there a reason you don't just sort it on the "main scorecard (all fses)" sheet directly? havocdragon wrote: 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? -- Dave Peterson -- Dave Peterson -- Dave Peterson |
| All times are GMT +1. The time now is 07:02 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com