Code to clear contents of a cell based on specific criteria

I have a report with the existing code. My report has many formulas making it very large. I use this code to export my data into a seperate workbook so I can forward the report to recipients. This code works perfectly for me and for sending to others. However, the export I send out does not work perfectly for others. When I send the report to the recipients, the recipients like to use it for vlookups in their reports to gather information from my report. When they add vlookups to their reports to pull information out of this report they get #Values for the blank cells from my report. When they clear the contents on the blank cells of my report their vlookups work fine. I am assuming when I use this code to export to an excel spreadsheet it is putting invisible characters in the blank cells.

Is there a way to add to the formula below that will clear the contents of the blank cells?


Sub TwoSheetsAndYourOut()
Dim NewName As String
Dim nm As Name
Dim ws As Worksheet

If MsgBox("Copy specific sheets to a new workbook" & vbCr & _
"New sheets will be pasted as values, named ranges removed" _
, vbYesNo, "NewCopy") = vbNo Then Exit Sub

With Application
.ScreenUpdating = False

On Error Resume Next
Sheets(Array("Summary", "Report", "Raw Data")).Copy
On Error Resume Next

For Each ws In ActiveWorkbook.Worksheets
ws.Cells.Copy
ws.[A1].PasteSpecial Paste:=xlValues
ws.Cells.Hyperlinks.Delete
Application.CutCopyMode = False
Cells(1, 1).Select
ws.Activate
Next ws
Cells(1, 1).Select

For Each nm In ActiveWorkbook.Names
nm.Delete
Next nm

NewName = InputBox("Please Specify the name of your new workbook", "New Copy", "Report ")

ActiveWorkbook.SaveCopyAs ThisWorkbook.Path & "\" & NewName & ".xlsx"
ActiveWorkbook.Close SaveChanges:=False

.ScreenUpdating = True
End With
Exit Sub

ErrCatcher:
MsgBox "Specified sheets do not exist within this workbook"
End Sub

Thank you for your time.

Please use code tags with your code.

Please Login or Register  to view this content.

INTO

Please Login or Register  to view this content.

Re: Code to clear contents of a cell based on specific criteria
Please use code tags with your code.


ws.Cells.Copy

INTO

ws.usedrange.Copy


I received feedback from one person stating this change didn't make a difference but I am keeping this option in going forward. I am starting to think it is the individual not the report or the code. Thank you so much for taking the time to review and answer my post.

It is greatly appreciated.

Originally Posted by AB33

"When they add vlookups to their reports to pull information out of this report they get #Values for the blank cells from my report"

Ask the user to include an iferror function on their vlookup formula so that if there is no match, the return value would be 0

There are 31 people this is sent out to and they all use it in different ways. I thought it would be easier to provide a product to all to use without issues rather than assisting to help each one.

[QUOTE=srsev;3484308]Re: Code to clear contents of a cell based on specific criteria
Please use code tags with your code.


ws.Cells.Copy

INTO

ws.usedrange.Copy


I received feedback from one person stating this change didn't make a difference but I am keeping this option in going forward. I am starting to think it is the individual not the report or the code. Thank you so much for taking the time to review and answer my post.

It is greatly appreciated.

Are not you the one who sends the report? If so, you know what the report looks like. When you run the report, do you get blank cells when pasted?

You can also add another to delete all rows which are blank, but I do not know what causes these blank cells to be in the first place.