Hi - I am trying to figure out the code to copy a range, paste it to the same
worksheet, sort the range, then copy the sorted data to another workhsheet.

This is what I have...

Private Sub ACSButton_Click()
ACSButton.Caption = "Click Here to Load ACS Services"
Worksheets("Solution Map").Activate
Worksheets("Solution Map").Range("Source1").Copy
ActiveSheet.Paste Destination:=Worksheets("Solution
Map").Range("Source2")
'Worksheets("Solution Map").Range("Source2").Select
'Worksheet("Solution Map").Range("Source2").Copy
Selection.Sort Key1:=Worksheets("Solution Map").Range("J36"),
Order1:=xlDescending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Selection.Copy
Worksheets("ACS").Activate
ActiveSheet.Paste Destination:=Worksheets("ACS").Range("Target")
Selection.PasteSpecial xlPasteValues
End Sub

But, it's giving me an 1004 error (The sort reference is not valid. Make
sure it's within the data you want to sort, and the first Sort By Box isn't
the same or blank.) on the "Selection.Sort" line.

Source1 is a named range in Solution Map
Source2 is the location where the copied data is pasted, the sorted, also in
Solution Map
I want to paste to a worksheet named ACS, to the named range "Target".

HELP!

Where and when are the ranges for Source1 and Source2 set?
Try inserting the following code as the first line and check that they are
in fact set.

MsgBox ("Source1 address is " & Source1.Address & _
Chr(13) & "Source2 address is " & Source2.Address)




"Shelly" wrote:

> Hi - I am trying to figure out the code to copy a range, paste it to the same
> worksheet, sort the range, then copy the sorted data to another workhsheet.
>
> This is what I have...
>
> Private Sub ACSButton_Click()
> ACSButton.Caption = "Click Here to Load ACS Services"
> Worksheets("Solution Map").Activate
> Worksheets("Solution Map").Range("Source1").Copy
> ActiveSheet.Paste Destination:=Worksheets("Solution
> Map").Range("Source2")
> 'Worksheets("Solution Map").Range("Source2").Select
> 'Worksheet("Solution Map").Range("Source2").Copy
> Selection.Sort Key1:=Worksheets("Solution Map").Range("J36"),
> Order1:=xlDescending, Header:=xlNo, _
> OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
> Selection.Copy
> Worksheets("ACS").Activate
> ActiveSheet.Paste Destination:=Worksheets("ACS").Range("Target")
> Selection.PasteSpecial xlPasteValues
> End Sub
>
> But, it's giving me an 1004 error (The sort reference is not valid. Make
> sure it's within the data you want to sort, and the first Sort By Box isn't
> the same or blank.) on the "Selection.Sort" line.
>
> Source1 is a named range in Solution Map
> Source2 is the location where the copied data is pasted, the sorted, also in
> Solution Map
> I want to paste to a worksheet named ACS, to the named range "Target".
>
> HELP!
>

I've given up on using the named ranges... and have gone back to referencing
by Row and Column.... here's what I have now (it's the ever-changing code)...

Sub ACSButton_Click()
ACSButton.Caption = "Click Here to Load ACS Services"
Sheets("Solution Map").Select
Range("F36:I201").Copy
Worksheets("Solution Map").Range("J36").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
True, Transpose:=False
Application.CutCopyMode = False
Range("J36:M201").Sort Key1:=Range("J36"), Order1:=xlDescending,
Key2:=Range( _
"K36"), Order2:=xlDescending, Key3:=Range("L36"),
Order3:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom
Selection.Copy
Sheets("ACS").Select
Range("A8").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
True, Transpose:=False
Range("A8").Select
End Sub

THanks.

Disregard my last advice. I confused myself with setting a range in code not
naming a range.

I have tested your code and it works. Check that the key range("J36") in the
sort is the correct first cell in the Source2.

I notice you have 2 lines commented out. You need to uncomment the first
line to select the range before sorting.
The second line not required but note it should have an s on the end of
worksheet if you use it.




"OssieMac" wrote:

> Where and when are the ranges for Source1 and Source2 set?
> Try inserting the following code as the first line and check that they are
> in fact set.
>
> MsgBox ("Source1 address is " & Source1.Address & _
> Chr(13) & "Source2 address is " & Source2.Address)
>
>
>
>
> "Shelly" wrote:
>
> > Hi - I am trying to figure out the code to copy a range, paste it to the same
> > worksheet, sort the range, then copy the sorted data to another workhsheet.
> >
> > This is what I have...
> >
> > Private Sub ACSButton_Click()
> > ACSButton.Caption = "Click Here to Load ACS Services"
> > Worksheets("Solution Map").Activate
> > Worksheets("Solution Map").Range("Source1").Copy
> > ActiveSheet.Paste Destination:=Worksheets("Solution
> > Map").Range("Source2")
> > 'Worksheets("Solution Map").Range("Source2").Select
> > 'Worksheet("Solution Map").Range("Source2").Copy
> > Selection.Sort Key1:=Worksheets("Solution Map").Range("J36"),
> > Order1:=xlDescending, Header:=xlNo, _
> > OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
> > Selection.Copy
> > Worksheets("ACS").Activate
> > ActiveSheet.Paste Destination:=Worksheets("ACS").Range("Target")
> > Selection.PasteSpecial xlPasteValues
> > End Sub
> >
> > But, it's giving me an 1004 error (The sort reference is not valid. Make
> > sure it's within the data you want to sort, and the first Sort By Box isn't
> > the same or blank.) on the "Selection.Sort" line.
> >
> > Source1 is a named range in Solution Map
> > Source2 is the location where the copied data is pasted, the sorted, also in
> > Solution Map
> > I want to paste to a worksheet named ACS, to the named range "Target".
> >
> > HELP!
> >