Hi Shila, based off the line that says there is more I am not able to test it but I think all you need to do is move a couple lines of code to make it work. After the the code I wrote runs it has a line that says END.
However we can't just remove the END to make it work. The END was there because in the event that your clipboard was empty the code need to go to the clipboardempty error. This is called an error handler. If the code runs without the need of the error handler then the command END is necessary to keep the code from running the handler at the very end. To fix it do the following:
I have a comment that says the following 'This is the command that deletes everything below.
You need to remove the END after the END IF that is after this line. Now once that is done it will continue to the code you pasted but there is one more thing to fix. The Error handler clipboardempty needs to go at the bottom of the code right before END SUB. you need to move all lines from clipboardempty: to where it says END. This is so the error handler is not gone but we dont want it to run right here. And that brings us to the final step. In the event that you do not need the error handler we need the code to end. You would write END right before the error handler. I understand this may be confusing but I wanted you to understand the why behind it. I have pasted the modified code below. Again I have not tested it but it seems like that should fix it.
Code:
Sub Report()
'IMPORTANT this code will DELETE data from a worksheet.
Dim c As Variant
Dim LastPasteRow As Long, FinalRow As Long, DataRows As Long
FinalRow = 30000 'Change this to the choice of 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
For Each c In Selection
LastPasteRow = c.Row
Next c
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
'NEXT clean data to generate report
'To remove the * from the file
Sheets("DATA").Select
Range("QueryData").Select
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:=1
' there is more but I just cut off - runs alone but when I combine the codes ends at paste, help please. Thanks
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
End
Clipboardempty:
'Checks if the clipboard is empty and if so halts execution
MsgBox "Nothing to paste, program ending."
End
End Sub
Let me know if that works.
Thanks