It sounds like the code is behind the worksheet with the button. If that's the
case, then unqualified objects will refer to the worksheet owning the code--not
the activesheet.

You can type out all the parents:

Sheets(newDataSheet).Range(shtRange).Sort _
Key1:=Sheets(newDataSheet).Range("E1"), Order1:=xlAscending, _


Or you could make the typing easier and just with the With/End with structure.
Notice the dots in front of the range objects. That means that they belong to
the object in the previous "with" statement.

Option Explicit
Private Sub CommandButton1_Click()
Dim TotalCols As Long
Dim TotalRows As Long
Dim shtRange As String
Dim newDataSheet As String

' Create Data Sheet
Call CopyWSToTempData ' this works

' Sort Data
newDataSheet = "TempData"
With Sheets(newDataSheet)
With .UsedRange
TotalCols = .Columns(.Columns.Count).Column
TotalRows = .Rows(.Rows.Count).Row
End With
shtRange = "A1:K" & TotalRows
With .Range(shtRange)
.Sort Key1:=.Range("E1"), Order1:=xlAscending, _
Key2:=.Range("F1"), Order2:=xlAscending, _
Key3:=.Range("G1"), Order3:=xlAscending, _
Header:=xlNo, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal, _
DataOption3:=xlSortNormal
End With
End With

End Sub

And you could drop all the .select statements and make it a bit easier to see
what's going on.

And if your data doesn't start in A1. your totalrows and totalcols variables
may not be what you expect. So I changed it.

ingineu wrote:

I'm trying to accomplish the following:


- Concatenate 4 Worksheets into 1 data sheet
- Sort the new data sheet

I have a separate worksheet that has a command button for initiating
the task. My problem lies in the SORT. For some reason it is trying
to sort the worksheet where the command button is located. Almost as
if the Select does not activate the 'TempData' worksheet. My code
follows.

Code:
--------------------
' Create Data Sheet
Call CopyWSToTempData ' this works

' Sort Data
newDataSheet = "TempData"
Sheets(newDataSheet).Select
totalcols = Sheets(newDataSheet).UsedRange.Columns.Count
totalrows = Sheets(newDataSheet).UsedRange.Rows.Count
shtRange = "A1:K" & totalrows
Sheets(newDataSheet).Range(shtRange).Select


Range(shtRange).Sort _
Key1:=Range("E1"), Order1:=xlAscending, _
Key2:=Range("F1"), Order2:=xlAscending, _
Key3:=Range("G1"), Order3:=xlAscending, _
Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:= _
xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _
DataOption3:=xlSortNormal
--------------------

Help would be appreciated. Thanks.

--
ingineu
------------------------------------------------------------------------
ingineu's Profile: http://www.excelforum.com/member.php...o&userid=14860
View this thread: http://www.excelforum.com/showthread...hreadid=513944

--

Dave Peterson