Hi Shila,
You may have found it. I completely forgot that the code I wrote was designed to look if there was something to delete. I didnt write it to have other commands after. You have done a great job with debugging. I think this code should work it only has a couple of changes but basically what you found is that the code was ending before the rest of the code could execute. The reason code will just stop is with the word End or Exit Sub. Now that all of the Ends and Exits have been removed except for the very end, the code should have no reason to just stop. Please let me know if this code ends up working I would love to add this learning to my knowledge.
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 = 30000 'You should just be able to leave this
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
DataRows = WorksheetFunction.CountA(Range(LastPasteRow + 1 & ":" & FinalRow))
If DataRows <> 0 Then
'This is the command that deletes everything below.
Range(LastPasteRow + 1 & ":" & FinalRow).ClearContents
End If
'removed condition that could end the program.
'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