Edit every worksheet in a workbook

[Uma Chauhan]

Hi Guys,

I am trying to run a piece of code in all the sheets in a workbook. However my code is only editing first sheet and then courses through other sheets but does not edit them. PFB the code:

Sub Worksheet_Editor()



For Each Worksheet In ThisWorkbook.Sheets


Cells.Select
Selection.Replace What:="NA", Replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.Delete Shift:=xlUp


Next

ActiveWorkbook.Save


End Sub

 

[MoshiM]

Hi Guys,

I am trying to run a piece of code in all the sheets in a workbook. However my code is only editing first sheet and then courses through other sheets but does not edit them. PFB the code:

Sub Worksheet_Editor()



For Each Worksheet In ThisWorkbook.Sheets


Cells.Select
Selection.Replace What:="NA", Replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.Delete Shift:=xlUp


Next

ActiveWorkbook.Save


End Sub

Try this.

Sub Worksheet_Editor()

Dim Worksheet As Worksheet

For Each Worksheet In ThisWorkbook.Sheets

    With Worksheet.UsedRange
    
        .Replace What:="NA", Replacement:="", LookAt:=xlWhole, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
            
        .SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp
    
    End With

Next

ActiveWorkbook.Save

End Sub

 

[Uma Chauhan]

I got an error 1004 for the line :

.SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp

There is nothing wrong with the actual code. The only problem is it is not going through all the sheets. I need it to run in all sheets in workbook

 

[MoshiM]

I got an error 1004 for the line :

.SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp

There is nothing wrong with the actual code. The only problem is it is not going through all the sheets. I need it to run in all sheets in workbook

I slightly edited your code to not require the use of Select. It works on my end so the error you are getting would just mean that there were no blank cells found. Place "on error resume next" before the loop starts.