Sorting two separate worksheets

[Bernice]

I'm want to sort two worksheets within the same workbook.
I have code that is working for each individual
worksheet. I'm trying to avoid having to physically be in
each worksheet.

Here is the code that I have - Worksheet 1
Dim shtworksheet As Worksheet
Dim LastRw As Long
Dim rngData As Range
Set shtworksheet = _
Application.Workbooks("test.xls").Worksheets("Current")
Set rngData = shtworksheet.Range("B6").CurrentRegion
LastRw = Range("B65536").End(xlUp).Offset(-15, 0).Row
Range("B6:T" & LastRw).Select
'sort data by purchase date
Selection.sort Key1:=Range("B6"), Order1:=xlAscending,
Header:=xlGuess
'sort by due date
'Selection.sort Key1:=Range("N6"), Order1:=xlAscending,
Header:=xlGuess
Range("O1").Select
ActiveCell.FormulaR1C1 = "PURCHASE DATE"

Worksheet 2
Dim shtworksheet1 As Worksheet
Dim LastRw As Long
Dim rngData As Range
Set shtworksheet1 = _
Application.Workbooks("test.xls").Worksheets("Matured")
Set rngData = shtworksheet1.Range("B6").CurrentRegion
LastRw = Range("A65536").End(xlUp).Row
Range("B6:T" & LastRw).Select
Selection.sort Key1:=Range("B6"), Order1:=xlAscending,
Header:=xlGuess
Range("O1").Select
ActiveCell.FormulaR1C1 = "PURCHASE DATE"

Any help would be greatly appreciated.

 

[Tom Ogilvy]

Worksheet 2
Dim shtworksheet1 As Worksheet
Dim LastRw As Long
Set shtworksheet1 = _
Application.Workbooks("test.xls").Worksheets("Matured")
LastRw = shtworksheet1.Range("A65536").End(xlUp).Row
shtworksheet1.Range("B6:T" & LastRw).sort _
Key1:=shtworksheet1.Range("B6"), Order1:=xlAscending, _
Header:=xlGuess, Range("O1")
shtWorksheet1.Range("B6").Formula = "PURCHASE DATE"

Make similar changes in the worksheet1 code.

 

[Bernice]

Tom,

Thanks for the information. I tried this in one of the
worksheets. I get a compile error: expected: named
paramenter on the Range("O1") part of the sort.

I tried taking out the Range("O1") and using
shtworksheet1.Range("O1").Select
ActiveCell.FormulaR1C1 = "PURCHASE DATE"

The sort is performed on the second worksheet, it stops
when trying to update the Purchase Date in O1 (Runtime
error 1004 Select method of Range class failed).

Any help you can provide would be appreciated.

Thanks,
Bernice

 

[Tom Ogilvy]

The range("O1") was an anomoly - it got lost in the clean up.

Worksheet 2
Sub Tester9()
Dim shtworksheet1 As Worksheet
Dim LastRw As Long
Set shtworksheet1 = _
Application.Workbooks("test.xls").Worksheets("Matured")
LastRw = shtworksheet1.Range("A65536").End(xlUp).Row
shtworksheet1.Range("B6:T" & LastRw).Sort _
Key1:=shtworksheet1.Range("B6"), Order1:=xlAscending, _
Header:=xlGuess
shtworksheet1.Range("O1").Formula = "PURCHASE DATE"
End Sub


This worked fine for me even when Matured was not the active sheet

 

[Bernice]

Tom,

Thanks for your help. I really appreciate it.

Bernice