Copy+Paste Special Single Cell on all sheets

Originally Posted by Steffen Thomsen

As i understood it, it was a different cell each time.

I figured it out using the following:

Code:

Sub CopyReplace()

Dim ws As Worksheet
For Each ws In Sheets
If ws.Visible Then ws.Select (False)
Next

ActiveCell.Copy
ActiveCell.PasteSpecial xlValues
Application.CutCopyMode = False

End Sub

EG1, If I'm reading your code right, what it's going to do is 1) Select each sheet, one at a time, then 2) determine which cell is the active cell on that sheet (which may or may not correspond with the active cell on the sheet you started with) and 3) convert that active cell to values.


You can do it like this. This would run faster, as there's no selecting, no copying, and no pasting, and it will hit the same location as your starting sheet.

Sub Values()
Dim sh As Worksheet
Dim addr As String
addr = ActiveCell.Address
For Each sh In ThisWorkbook.Worksheets
sh.Range(addr).Value = sh.Range(addr).Value
Next
End Sub