PDA

View Full Version : Solved: sort activeworksheet, out of range



mikeo1313
08-17-2010, 02:27 PM
How can I change this macro to work with the "activeworksheet" instead of only with "almonds.csv" ?

1. In the beginning it sorts the first column (except headers).
2. adds 13 columns
3. copies and pastes headers from another workbook ("book1") back to "almongs.csv"

The amount of rows will vary with different files also (unlike in vba below) though all rows are desired to be sorted.




Sub ImgGen13col()
'
' ImgGen13col Macro
'
' Keyboard Shortcut: Ctrl+p
'
ActiveWorkbook.Worksheets("almonds").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("almonds").Sort.SortFields.Add Key:=Range("A1"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("almonds").Sort
.SetRange Range("A2:X47")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Selection.EntireColumn.Insert
Selection.EntireColumn.Insert
Selection.EntireColumn.Insert
Selection.EntireColumn.Insert
Selection.EntireColumn.Insert
Selection.EntireColumn.Insert
Selection.EntireColumn.Insert
Selection.EntireColumn.Insert
Selection.EntireColumn.Insert
Selection.EntireColumn.Insert
Selection.EntireColumn.Insert
Selection.EntireColumn.Insert
Selection.EntireColumn.Insert
Windows("Book1.xlsx").Activate
ActiveWindow.WindowState = xlMaximized
Range("A1:M1").Select
Range("M1").Activate
Selection.Copy
Windows("almonds.csv").Activate
ActiveSheet.Paste
End Sub

Bob Phillips
08-17-2010, 02:50 PM
Sub ImgGen13col()
'
' ImgGen13col Macro
'
' Keyboard Shortcut: Ctrl+p
'
Dim sh As Worksheet

Set sh = ActiveSheet

With sh

With .Sort

.SortFields.Clear
.SortFields.Add Key:=Range("A1"), _
SortOn:=xlSortOnValues, _
Order:=xlAscending, _
DataOption:=xlSortNormal
.SetRange Range("A2:X47")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End With
Selection.Resize(, 13).EntireColumn.Insert
Windows("Book1.xlsx").Activate
ActiveWindow.WindowState = xlMaximized
Range("A1:M1").Copy sh.Range("A1")
End Sub

mikeo1313
08-17-2010, 02:55 PM
I know that 47 is the amount of rows in almonds.csv

I have files I want to run this macro in that exceed that amount of rows.

Thats why I'm suspicious of these lines



DataOption:=xlSortNormal
.SetRange Range("A2:X47")





Is it mandatory to specify the range if you want to sort the whole worksheet? If not, I imagine it would take finding out how many rows the active worksheet has before sorting.

Thank you for your time.

mikeo1313
08-17-2010, 03:19 PM
would an arbitrarily high number do the trick without causing issues or abnormailities in the worksheet?

for example

.SetRange Range("A2:Z54321")

rbrhodes
08-17-2010, 06:32 PM
Hi,

Add these lines to XLD's code:


Sub ImgGen13col()
'
' ImgGen13col Macro
'
' Keyboard Shortcut: Ctrl+p
'
Dim sh As Worksheet

'//Add
Dim LastRow As Long
'//

Set sh = ActiveSheet

With sh

'//Add
lastrow = .range("A" & rows.count).end(xlup).row
'//
With .Sort

.SortFields.Clear
.SortFields.Add Key:=Range("A1"), _
SortOn:=xlSortOnValues, _
Order:=xlAscending, _
DataOption:=xlSortNormal
.SetRange Range("A2:X47")

<snip>



and change the last line in the snippet above:


.SetRange Range("A2:X47")

'//Change to:
.SetRange Range("A2:X" & lastrow)
'//



Looks like you may need a sub to open and close a series of wb's as well but that's beyond the scope of what I do for free.