Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting Existing Totals Into A New & Separate Column | Excel Worksheet Functions | |||
Sorting "State" Column onto separate worksheets in same workbook. | Excel Worksheet Functions | |||
Save 2 separate data imports in separate worksheets on the same ex | Excel Worksheet Functions | |||
sorting data into separate worksheets | Excel Worksheet Functions | |||
Separate Column Sorting | Excel Worksheet Functions |