ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Sorting two separate worksheets (https://www.excelbanter.com/excel-programming/291179-sorting-two-separate-worksheets.html)

Bernice[_2_]
Sorting two separate worksheets
 
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("Curr ent")
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("Matu red")
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
Sorting two separate worksheets
 

Worksheet 2
Dim shtworksheet1 As Worksheet
Dim LastRw As Long
Set shtworksheet1 = _
Application.Workbooks("test.xls").Worksheets("Matu red")
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.

--
Regards,
Tom Ogilvy


"Bernice" wrote in message
...
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("Curr ent")
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("Matu red")
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.

Bernice[_2_]
Sorting two separate worksheets
 
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

-----Original Message-----

Worksheet 2
Dim shtworksheet1 As Worksheet
Dim LastRw As Long
Set shtworksheet1 = _
Application.Workbooks("test.xls").Worksheets("Matu red")
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.

--
Regards,
Tom Ogilvy


"Bernice" wrote in
message
...
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
Sorting two separate worksheets
 
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("Matu red")
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

--
Regards,
Tom Ogilvy


"Bernice" wrote in message
...
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

-----Original Message-----

Worksheet 2
Dim shtworksheet1 As Worksheet
Dim LastRw As Long
Set shtworksheet1 = _
Application.Workbooks("test.xls").Worksheets("Matu red")
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.

--
Regards,
Tom Ogilvy


"Bernice" wrote in
message
...
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.


.

Bernice[_2_]
Sorting two separate worksheets
 
Tom,

Thanks for your help. I really appreciate it.

Bernice
-----Original Message-----
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("Matu red")
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

--
Regards,
Tom Ogilvy


"Bernice" wrote in
message
...
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

-----Original Message-----

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.

--
Regards,
Tom Ogilvy


"Bernice" wrote
in
message
...
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.


.



.


All times are GMT +1. The time now is 05:44 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com