delete blank data from a pivot related workbook

[memory100]

i have pivot table in a excel sheet and it pulls data from a CRM database.

when a user clicks any data in the pivot table it creates a new worksheet showing all the data that is related to that data selected.

I want to know is how can i delete the epmty cells in this new work sheet can i make this automatic for a "x" number of new sheets ?? i have tried these codes with no success!

Private Sub Worksheet_Change(ByVal Target As Range)

'Deletes blank rows as they occur.

 

 'Prevent endless loops

 Application.EnableEvents = False

 'They have more than one cell selected

 If Target.Cells.Count > 1 Then GoTo SelectionCode

  If WorksheetFunction.CountA(Target.EntireRow) = 0 Then

   Target.EntireRow.Delete

  End If

 

 Application.EnableEvents = True

 'Our code will only enter here if the selection is more than one cell.

 Exit Sub

   

SelectionCode:

 If WorksheetFunction.CountA(Selection.EntireRow) = 0 Then

  Selection.EntireRow.Delete

 End If

 Application.EnableEvents = True

End Sub

i tried to paste this into a new module in microsft visyak basic so i can then run the macro in excel for some reason it does not show this code to be reconized as a macro!

i tired ti paste this code into "microsft excel objects" in vb still get no success in deleting blank cells in the worksheet!