[rsdjimbie]

I have macros running but sometimes #N/A errors stop the running macro and I get lost with the current update. I need a macro that will run through the sheet and change all #N/A errors to a blank cell. Please find attached a sheet showing my problem.
Many thanks.

[JBeaucaire]

1) Select all the data
2) Press F5
3) Select Constants > Errors (uncheck the rest)
4) Press delete


You can record those simple steps into a macro if you really need to.

[rsdjimbie]

Thank you JB!

[rsdjimbie]

Ok now I cannot handle it any more. When the macro finds no #NA errors it stops with a error! Any ideas?
Please have a look at attached file with button.

[rsdjimbie]

Attachment

[JBeaucaire]

1) Use this instead:

Sub DelNAErrorsOnEmbSheet()
On Error Resume Next
    
    Sheets("Emb").Activate
    Cells.SpecialCells(xlCellTypeFormulas, 16).ClearContents
    
End Sub

2) I noticed all your macros do a lot of UNPROTECT/PROTECT activity. There's no need to do that.

Excel has a hidden protection flag called UserInterfaceOnly that sets the protection to humans only. That leave VBA free to do what it wants on the same sheet(s) protected this way.

The only downside is that this flag does not survive closing and reopening the workbook. But that's easy to fix with a macro that resets the flag on each sheet needed each time the workbook is opened.

Put this macro into the ThisWorkbook module and edit the Array() to list the sheet names you need to reset the protection flag on.

Private Sub Workbook_Open()
Dim ws As Worksheet

    For Each ws In Sheets(Array("Emb", "Prod A", "Prod B"))
        ws.Protect DrawingObjects:=True, Contents:=True, _
            Scenarios:=True, UserInterfaceOnly:=True
    Next ws

End Sub

3) Your macros do a LOT of selecting. You can and should edit all of that out to increase the efficiency and readability of your macros. For instance, here's your Module39 macro after I edited it down:

Sub UpdateSchedule()
Application.ScreenUpdating = False
 
    ActiveSheet.Unprotect
    Columns("L:L").Font.ColorIndex = 0
    Columns("F:F").Copy
    Columns("A:A").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, _
        Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    
    Range("N401:AB401").Copy
    Range("N3:N400").Select
    Range("N400").Activate
    ActiveSheet.Paste
    
    Range("H2") = 1
    Range("H2").Interior.ColorIndex = xlNone
    Range("H2").Copy
    Range("A3:A401,K3:K401").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, _
        Operation:=xlMultiply, SkipBlanks:=False, Transpose:=False
    Application.CommandBars("Task Pane").Visible = False
    Application.ReplaceFormat.Clear
    Range("A3:A401,K3:K401").Replace What:="0", Replacement:="", LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    
    Range("A300") = "Line D"
    Range("A200") = "Line C"
    Range("A100") = "Line B"
    Range("B3:B401").Replace What:="EMB", Replacement:="Emb", LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Range("B3:B401").Replace What:="PRINT", Replacement:="Print", LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Range("B3:B401").Replace What:="PRT", Replacement:="Print", LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Range("B3:B401").Replace What:="RIVET", Replacement:="Riv", LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Range("B3:B401").Replace What:="STUDS", Replacement:="Stud", LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Range("B3:B401").Replace What:="BUTTON", Replacement:="Stud", LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    
    Range("A2") = "MH no"
    Range("B2") = "O/W"
    Range("C2") = "Del"
    Range("D2") = "Fab"
    Range("K2") = "# no"
    Range("L2") = "Style & wash"
    Range("M2") = "Qty"

    Range("A3:A99,A101:A199,A201:A299,A301:A401").Font.ColorIndex = 3
    
    Columns("F:F").ClearContents
    Range("A3").Select
    ActiveSheet.Protect
    Application.ScreenUpdating = True
    
    MsgBox "Updated!"
End Sub

You could remove the Unprotect/Protect stuff, too, if the sheet this macro runs on is added to the ThisWorkbook macro array of sheets I gave you earlier.


4) Your Module42 has a lot of copy/pastevalues. This, too, can be sped up by just using the .Value property of the cell your putting the data into. Like so:

Sub MonthEndUpdate()
Application.ScreenUpdating = False
    Sheets("SPPA").Activate
   
    Range("G31").Value = Range("I18").Value
    Range("G54").Value = Range("I41").Value
    Range("G76").Value = Range("I63").Value
    Range("G98").Value = Range("I85").Value
    Range("G30").Value = Range("G24").Value
    Range("G32").Value = Range("G28").Value
    Range("G33").Value = Range("G29").Value
    Range("G53").Value = Range("G47").Value
    Range("G55").Value = Range("G51").Value
    Range("G56").Value = Range("G52").Value
    Range("G75").Value = Range("G69").Value
    Range("G77").Value = Range("G73").Value
    Range("G97").Value = Range("G91").Value
    Range("G99").Value = Range("G95").Value
    Range("G100").Value = Range("G96").Value
    Range("G78").Value = Range("G74").Value
    Range("G101").Value = Range("L86").Value
    Range("G79").Value = Range("L64").Value
    Range("G34").Value = Range("L19").Value
   
    Range("I12,J12:J13,I18,J18:J19,I41,J41:J42,I63,J63:J64,I85,J85:J86,J109,K103:L104") = "0"
    Range("K105:L106").Select
    Sheets("HO plan").Select
    Range("R47") = Range("R47") + Range("R43")
    Application.ScreenUpdating = True
    MsgBox "Updated!"
End Sub

[rsdjimbie]

Jeez was not necessary to do all that work, but thank you very much. I am not very good at macros and I am teaching myself, I have learned so much from you already.

[JBeaucaire]

We are a village... and this is a teaching forum, so we're doing good, yes?

[rsdjimbie]

I am struggeling with "UserInterfaceOnly= True" where I have a password to unprotect. It keeps on asking for the pass though. Otherwise if no pass no problem.

[JBeaucaire]

UserInterfaceOnly:=True is a parameter you set in a PROTECT command, not an unprotect command.

Once you PROTECT a sheet and include this flag when you do it, you no longer need to unprotect that sheet for the rest of the day. Is that clear what I mean?

The point is to never have to bother unprotecting a sheet, all your macros have a free pass to do what they want event though the sheet is protected from humans changing it.

So protect your sheet with that flag added, and remove all the unprotect codelines for that sheet and you're good to go.

[rsdjimbie]

It is clear and I understand, and it works fine if there is no password when the sheet is protected, otherwise before the macro will continues to do its work asks for the password.

[JBeaucaire]

This is not correct. If you are experiencing this behavior then something is going on unexplained or improperly implemented. I'd have to see what you're seeing.

[rsdjimbie]

Refer " Packing list" sheet where there is a button " "Email to humin", this is so that the file can be made smaller to email and also to restrict usage of the file and all my work put into this freely. It always asks for the password. Hope this will clear things up. I had to clear some sheets to save space, no information needed for this macro anyway.The pass on all sheets is 123. Many thanks JB.

[JBeaucaire]

1) You failed to ADD the password to the workbook open macro so the flags could be set properly when the workbook first opens.

Private Sub Workbook_Open()
Dim ws As Worksheet

    For Each ws In Sheets(Array("Recording", "SAD500-1st", "2nd", "3d", "4th", "5th"))
        ws.Protect "123", DrawingObjects:=True, Contents:=True, _
            Scenarios:=True, UserInterfaceOnly:=True
    Next ws

End Sub

3) The password on sheets SAD500-1st was wrong, I had to set that to 123 to get it to work, you had it as 1234.

4) Your macro Email to Humin still had a ActiveSheet.Unprotect line of code in it. Didn't I say to remove all unprotect code lines?

5) The sheet EDI is one you're trying to edit, but it's NOT one of the sheets in your workbook_open macro. Add it to the array of sheets if it's one you want editable by VBA.

Add any others, too.

Private Sub Workbook_Open()
Dim ws As Worksheet

    For Each ws In Sheets(Array("Recording", "SAD500-1st", "2nd", "3d", "4th", "5th", "EDI"))
        ws.Protect "123", DrawingObjects:=True, Contents:=True, _
            Scenarios:=True, UserInterfaceOnly:=True
    Next ws

End Sub

Or, remove the protection from that sheet.


6) Some edits on the Email to Humin macro:

Sub EmailReadyToHumin()
'
' EmailReadyToHumin Macro
'

'
Application.ScreenUpdating = False
Application.DisplayAlerts = False
With Sheets("Invoice")
    .Cells.Value = .Cells.Value
End With

With Sheets("EDI")
    .Cells.Value = .Cells.Value
End With
    
    Sheets(Array("Recording", "SAD500-1st", "2nd", "3d", "4th", "5th")).Select
    Sheets("5th").Activate
    ActiveWindow.SelectedSheets.Delete
    Sheets("Packing list").Select
    Range("A1").Select
    
'To save on any desktop and per cells content
Dim fName As String, DTAddress As String
    DTAddress = CreateObject("WScript.Shell").SpecialFolders("Desktop") & Application.PathSeparator
'And per cells content
    fName = Range("L3").Text & "Packing list"

    ActiveWorkbook.SaveAs DTAddress & fName & ".xls"
    
    MsgBox "Saved and ready to email from desktop!"

End Sub

[rsdjimbie]

Thank you the macro is running smoothly, jip I'm still missing simple things and cause me huge problems!