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 initiatin
the task. My problem lies in the SORT. For some reason it is tryin
to sort the worksheet where the command button is located. Almost a
if the Select does not activate the 'TempData' worksheet. My cod
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:=xlSortNorma
-------------------


Help would be appreciated. Thanks

--
ingine
-----------------------------------------------------------------------
ingineu's Profile: http://www.excelforum.com/member.php...fo&userid=1486
View this thread: http://www.excelforum.com/showthread.php?threadid=51394

Hi,
It worked OK for me but try:

Sheets(newDataSheet).Range(shtRange).Sort _

and remove/comment out:

Sheets(newDataSheet).Range(shtRange).Select

HTH
(XL2003)
"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

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

Yes, you're right, the button is located on a separate sheet. I used
the record macro to come up with ideas on how to do the sort. I
thought doing a SELECT would activate the worksheet.
Your code totally makes sense. Thank you for taking the time.


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

Sorry, forgot to ask ...
And if your data doesn't start in A1. your totalrows and totalcols
variables
may not be what you expect. So I changed it.
I'm not sure what the difference is between the 2 methods. When you
say "data doesn't start in A1", are you referring to blank lines?


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

Try this code:

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim TotalRows As Long
Dim TotalCols As Long

Set wks = Worksheets.Add

With wks
.Range("x21:z30").Value = "x"

TotalRows = .UsedRange.Rows.Count
TotalCols = .UsedRange.Columns.Count
MsgBox "Test#1:" & vbLf & _
"Column: " & .UsedRange.Columns.Count & vbLf & _
"row: " & .UsedRange.Columns.Count & vbLf & _
"Address: " & .Range("A1", .Cells(TotalRows, TotalCols)).Address

With .UsedRange
TotalRows = .Rows(.Rows.Count).Row
TotalCols = .Columns(.Columns.Count).Column
End With
MsgBox "Test#2:" & vbLf & _
"Column: " & .UsedRange.Columns.Count & vbLf & _
"row: " & .UsedRange.Columns.Count & vbLf & _
"Address: " & .Range("A1", .Cells(TotalRows, TotalCols)).Address
End With

End Sub

It creates a new sheet and populates a range with some x's. But A1 doesn't get
populated.

Excel is smart enough to keep track of the usedrange--if you use A1, then clear
it, excel still thinks that the .usedrange starts in A1. But it may not always
be true in all cases.

Sometimes the .usedrange doesn't start in row 1 or column A.


ingineu wrote:

Sorry, forgot to ask ...
And if your data doesn't start in A1. your totalrows and totalcols
variables
may not be what you expect. So I changed it.
I'm not sure what the difference is between the 2 methods. When you
say "data doesn't start in A1", are you referring to blank lines?

--
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

I messed up my rows and columns (doh!).

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim TotalRows As Long
Dim TotalCols As Long

Set wks = Worksheets.Add

With wks
.Range("x21:z30").Value = "x"

TotalRows = .UsedRange.Rows.Count
TotalCols = .UsedRange.Columns.Count
MsgBox "Test#1:" & vbLf & _
"Column: " & TotalCols & vbLf & _
"row: " & TotalRows & vbLf & _
"Address: " & .Range("A1", .Cells(TotalRows, TotalCols)).Address

With .UsedRange
TotalRows = .Rows(.Rows.Count).Row
TotalCols = .Columns(.Columns.Count).Column
End With
MsgBox "Test#2:" & vbLf & _
"Column: " & TotalCols & vbLf & _
"row: " & TotalRows & vbLf & _
"Address: " & .Range("A1", .Cells(TotalRows, TotalCols)).Address
End With

End Sub



Dave Peterson wrote:

Try this code:

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim TotalRows As Long
Dim TotalCols As Long

Set wks = Worksheets.Add

With wks
.Range("x21:z30").Value = "x"

TotalRows = .UsedRange.Rows.Count
TotalCols = .UsedRange.Columns.Count
MsgBox "Test#1:" & vbLf & _
"Column: " & .UsedRange.Columns.Count & vbLf & _
"row: " & .UsedRange.Columns.Count & vbLf & _
"Address: " & .Range("A1", .Cells(TotalRows, TotalCols)).Address

With .UsedRange
TotalRows = .Rows(.Rows.Count).Row
TotalCols = .Columns(.Columns.Count).Column
End With
MsgBox "Test#2:" & vbLf & _
"Column: " & .UsedRange.Columns.Count & vbLf & _
"row: " & .UsedRange.Columns.Count & vbLf & _
"Address: " & .Range("A1", .Cells(TotalRows, TotalCols)).Address
End With

End Sub

It creates a new sheet and populates a range with some x's. But A1 doesn't get
populated.

Excel is smart enough to keep track of the usedrange--if you use A1, then clear
it, excel still thinks that the .usedrange starts in A1. But it may not always
be true in all cases.

Sometimes the .usedrange doesn't start in row 1 or column A.

ingineu wrote:

Sorry, forgot to ask ...
And if your data doesn't start in A1. your totalrows and totalcols
variables
may not be what you expect. So I changed it.
I'm not sure what the difference is between the 2 methods. When you
say "data doesn't start in A1", are you referring to blank lines?

--
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

--

Dave Peterson

Thanks .. so glad you caught that one.


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