Macro help from Barrie or anyone please! :)

ok...

Please ignore the 3rd question about clearing contents... I swear it didnt work at first, but now it does fine, typical computer stuff. But I'm still stuck on the other two, thanks :)

ok i lied

The clear contents DOESN'T work. It does clear, but gives an error when the macro tries to paste. My original post is the correct one with the correct questions. Thanks

Hi Rob, one question. What do you mean by:
"Is there a way to clear the contents of the selected range at the end before it pastes?"

What contents do you want cleared? Contents in the sheet named "Returned sort"? What range in that sheet?

Barrie

Hi Barrie,
Sorry if I was unclear. What I'm trying to do is, if I do the sort More than one time, but with different criteria, but want it to go to the same sheet. So, before i paste the new data in the "Returned Sort" sheet, I need to clear that sheet or it will only paste on top of the current data, which will sometimes leave data from the previous sort if the new paste is shorter than the data already on that sheet.

In any case, i think i solved this. I tried doing the clear BEFORE the macro copied the data, and it seems to be working. For some reason, if i let it copy first, then cleared contents, then tried to paste it gave an error. so i THINK i have this part figured out. Still stuck on the others though :)

Thanks again Barrie

Okay Rob, try this instead (I made some changes to the macro posted).

Sub Button1_Click()
'Macro written by Barrie Davidson

'Variables used by the macro
Dim FilterCriteria
Dim ColumnFilterSelect
Dim CurrentSheetName As String
Dim RangeSelect As String

'Defines Range Size with column F being the last column of data
RangeSelect = "A1:" & Range("F65536").End(xlUp).Address
'Get the current sheet's name
CurrentSheetName = ActiveSheet.Name
'Clears the sheet named Returned Sort
Sheets("Returned Sort").Activate
Cells.Clear
Sheets(CurrentSheetName).Activate
'Select the defined range
Range(RangeSelect).Select
'Apply Autofilter
Selection.AutoFilter
GetColumnInfo:
'Asks the user to choose which column to sort by <----sort by first column headers?
ColumnFilterSelect = InputBox("Enter the Header you wish to Sort")
For Each c In Range("A1:F1")
If c.Value = ColumnFilterSelect Then
ColumnFilterSelect = c.Column
End If
Next c
If IsNumeric(ColumnFilterSelect) = False Then
MsgBox ("Header entered does not exist")
GoTo GetColumnInfo
End If
'Get the filter's criteria from the user
FilterCriteria = InputBox("Enter the Condition to Sort out:")
'Filter the data based on the user's input
Selection.AutoFilter field:=ColumnFilterSelect, Criteria1:=FilterCriteria

'Select the visible cells (the filtered data)
Selection.SpecialCells(xlCellTypeVisible).Select
'Copy the cells
Selection.Copy

'Selects the "Returned Sort" Sheet which will have the results of the sort
Sheets("Returned Sort").Activate
'Selects the defined range<------ clear contents first?
'Range(RangeSelect).Select
'Selection.ClearContents

'Make sure you are in cell A1
Range("A1").Select
'Paste the copied cells
ActiveSheet.Paste
'Go to A1
Range("A1").Select
'Clear the clipboard contents
Application.CutCopyMode = False
'Go back to the original file
Sheets(CurrentSheetName).Activate
'Clear the autofilter
Selection.AutoFilter field:=ColumnFilterSelect
'Take the Autofilter off
Selection.AutoFilter
'Go to A1
Range("A1").Select

End Sub

Barrie

Barrie

Barrie,
Thanks for the revisions. One thing, the code you posted does let me select the header, but when the macro finishes, and i look at the "Returned Sort" sheet which should have the data returned, there is nothing there. I'm assuming something is going wrong with the "getcolumninfo" section. I'm trying to figure it out, but i may need some help :) thanks

If you need help just let me know (you have my e-mail address)