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