CODEWITHSUNDEEP
excelvbareplace
cycla
cycla

Reputation: 147

How to replace all cells in multiple worksheet except one vba

What I want to do is to replace all cell values in the multiple worksheets except one. So For example, there are worksheets sheet1, sheet2, sheet3, and I want to replace all the values "M9" into "M8", but leave M9 in Sheet1 remains unchanged.

This is my code:

`For Each Worksheet in WorkSheets

     If Worksheet <> ThisWorkbook.Worksheets("Sheet1") Then
          Worksheet.Cells.Replace .....
     End if
 Next`

For For-Next, Just ignore the details as it is related to my contents. I thought this logic would have been right, but vba keep on coming up with error.

Do you know why? And are there any right way to replace the values excluding Sheet1?

Thanks for your help

Upvotes: 0

Views: 378

Answers (1)

FaneDuru
FaneDuru

Reputation: 42236

If you want clearing all cells content, as I suppose, please use the next way. It also shows the way of replacing:

Sub RemoveCellsValue()
  Dim ws As Worksheet
  For Each ws In ThisWorkbook.Worksheets
     If ws.Name <> "Sheet1" Then
          ws.cells.ClearContents
          'or
          'ws.Cells.Replace "Oldstring","NewString" 'if you need replacing
     End If
 Next
End Sub

If replacing, the function needs some more parameters. Especially to state if you need to replace if the whole string is found, or only part of it.

Upvotes: 1

Related Questions