OK I think my version of Excel is too old to process your sort commands or you have some other formatting that isnt on this example. In any case I changed the code a little bit and made some things a little more efficient since we are always starting at row 1. copy and paste this code into your main workbook, save a backup and try it. if it halts again make note of the error that comes up and let me know what it says. If the sorting part is not the issue then this should work.
Code:
Sub PasteDeleteandcleandata()
'Please note the QueryData is the range of data.
'I used the offset formula in the formulas to define name and bind the data should it grow in size or reduce.
'You can change it to the table range of the Data sheet which is A1:L30 in this test code.
'When I run the code without the paste delete code - it works fine but with the paste delete, it halts. Appreciate the help.
'IMPORTANT this code will DELETE data from a worksheet. Be sure to only have the affected workbook open
'when running.
Dim c As Variant
Dim LastPasteRow As Long, FinalRow As Long, DataRows As Long
FinalRow = 3000 'Change this to the final row of data.
On Error GoTo Clipboardempty
Range("A1").Select
ActiveSheet.Paste
On Error GoTo 0 'return errors back to normal.
'Run a loop to find the last row
LastPasteRow = Selection.Rows.Count 'was able to remove the loop since
'we always start on row 1
DataRows = WorksheetFunction.CountA(Range(LastPasteRow + 1 & ":" & FinalRow))
If DataRows = 0 Then
End 'Ends the program because nothing is below
Else
'This is the command that deletes everything below.
Range(LastPasteRow + 1 & ":" & FinalRow).ClearContents
End If
'To remove the * from the file
Sheets("DATA").Select
Range("QueryData").Select 'Please note the QueryData is the range of data - I used the offset formula in the formulas for the named manager.
Columns("F:F").Select
Selection.Replace What:="~*", Replacement:="", LookAt _
:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:= _
False, ReplaceFormat:=False
'To Custom Sort the Subcategories in the right order needed in the Reports
ActiveWorkbook.Worksheets("DATA").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("DATA").Sort.SortFields.Add Key:=Range("F:F"), _
SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
"Banana,Apple,Tomatoes,Spices" _
, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("DATA").Sort
.SetRange Columns("F:F")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
'This refreshes all Pivot Table Element for Report
ActiveWorkbook.RefreshAll
'This is to autofit the sheet
Set mysheet = ActiveSheet
For Each Sheet In Worksheets
Sheet.Select
Cells.EntireColumn.AutoFit
Next Sheet
mysheet.Select
'To select the first cell
Sheets("Sort").Select
Exit Sub
Clipboardempty:
'Checks if the clipboard is empty and if so halts execution
MsgBox "Nothing to paste, program ending."
End
End Sub