selection.Sort error

[photon_ed]

Hello,
Can someone tell me as to why microsoft VB seems to have a problem running the line "Selection.Sort Key1:=Range("h1"), ..." in the program below. However, the line of code runs stand alone. Please advise. Thank you.

yours,
Ed






Sub Daily_Click()



'copyAndPasteColumns
'------------------------------------------------------
'------------------------------------------------------
Dim inData As Integer

Data = Array(1, 3, 5, 7, 9, 11, 13, 24, 32, 33, 34, 35, 36, 37, 38, 39, 40, 44, 47, 51, 52, 53, 54, 0)
inData = 0

Sheets(1).Select
ActiveSheet.Range("a1").Select
Sheets(2).Select
ActiveSheet.Range("a1").Select
Do Until Data(inData) = 0
ActiveSheet.Columns(Data(inData)).Copy
Sheets(1).Select
ActiveSheet.Paste
ActiveCell.Offset(0, 1).Select
Sheets(2).Select
'MsgBox Data(inData)
inData = inData + 1
Loop
'------------------------------------------------------
'------------------------------------------------------


Sheets(1).Select
ActiveSheet.Range("h1").Select
Selection.Sort Key1:=Range("h1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom


Sheets(1).Select
ActiveSheet.Range("a1").Select
ActiveCell.EntireRow.Insert
Sheets(4).Select
ActiveSheet.Range("a1").Select
ActiveSheet.Rows(1).Copy
Sheets(1).Select
ActiveSheet.Range("a1").Select
ActiveCell.EntireRow.Insert
'ActiveSheet.Paste


ActiveSheet.Columns("P:Q").Select
Selection.Style = "percent"
Selection.NumberFormat = "0.00%"
ActiveWindow.DisplayGridlines = False
ActiveSheet.Columns.Select
ActiveSheet.Columns.AutoFit
ActiveCell.Range("a1").Select
ActiveWorkbook.Save


End Sub

[Norie]

Ed

What range do you actually want to sort?

What's actually selected when the code errors?

Where are you copying/pasting from/to?

Option Explicit
Sub Daily_Click()
Dim inData As Long
Dim arrData


'copyAndPasteColumns
'------------------------------------------------------
'------------------------------------------------------

arrData = Array(1, 3, 5, 7, 9, 11, 13, 24, 32, 33, 34, 35, _
36, 37, 38, 39, 40, 44, 47, 51, 52, 53, 54)

For inData = LBound(arrData) To UBound(arrData)
Sheets(2).Columns(arrData(inData)).Copy Sheets(1).Range("A1").Offset(inData)
Next

With Sheets(1)
.UsedRange.Sort Key1:=.Range("h1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
.Range("a1").EntireRow.Insert
Sheets(4).Rows(1).Copy .Range("A1")
End With

ActiveCell.EntireRow.Insert
With ActiveSheet ' not sure which sheet to use here, change ActiveSheet to Sheets(1) or whatever

.Columns("P:Q").Style = "percent"
.Columns("P:Q").NumberFormat = "0.00%"
ActiveWindow.DisplayGridlines = False
.Columns.Columns.AutoFit
.Range("a1").Select
End With

ActiveWorkbook.Save

End Sub

[lucas]

not sure what your doing here either.....maybe I'm missing something:

Sheets(1).Select
ActiveSheet.Range("a1").Select
Sheets(2).Select
ActiveSheet.Range("a1").Select

[photon_ed]

thank you for the prompt replies.
Steve:; I was only setting activecell to cell A1, its easier for me to know what i am do since i am such a beginner.
Norie: the error occures at

Selection.Sort Key1:=Range("h1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

HOwever, I will have a go at your codes and get back to you.

Many thanks guys :)

yours,
Ed

[lucas]

somewhere in this loop(I think)you are removing the data from H1 in sheet1 so it doesn't have anything to act on.....


Do Until Data(inData) = 0
ActiveSheet.Columns(Data(inData)).Copy
Sheets(1).Select
ActiveSheet.Paste
ActiveCell.Offset(0, 1).Select
Sheets(2).Select
'MsgBox Data(inData)
inData = inData + 1
Loop

it does run stand alone if there is data in h1. When I ran your code from above that gives you an error...after the error there was no data in h1 sheet1 after the loop

if you try to run the sort routine with no data in h1 sheet1 you will get the same error