Copy+Paste Special Single Cell on all sheets

Hey,

Can anyone help me write a macro that will take the cell I have currently selected and copy and paste special the value in that cell and then do the same on each sheet in the workbook? Each sheet will have a different number to copy/paste special, but the cell location will be the same across each sheet.

eg1,

I'm not sure I understand what you want. Are you simply wanting to convert the selected cell to values? Maybe like you click on A3, which is a formula, and you want to replace it with the value? Is that what you're looking for?

Originally Posted by jomili

eg1,

I'm not sure I understand what you want. Are you simply wanting to convert the selected cell to values? Maybe like you click on A3, which is a formula, and you want to replace it with the value? Is that what you're looking for?

Exactly. But I have about 50+ sheets in the workbook that all need a single cell to be converted from a formula to just a numeric value.

I was able to get this far just by recording in relative reference mode, but I do not know how to get it to repeat on all sheets:



Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Originally Posted by Steffen Thomsen

You can use this

Sub CopyReplace()

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

End Sub

This worked for the page I was on but I would like to to repeat for all sheets in the workbook.

Or

Activecell.value = Activecell.value

By "Repeat on every sheet" are you saying that the value in A3 in sheet1 needs to pasted into each sheet in A3?
Or are you saying the value in A3 on every sheet needs to be converted to it's value?

Originally Posted by jomili

Or

Activecell.value = Activecell.value

By "Repeat on every sheet" are you saying that the value in A3 in sheet1 needs to pasted into each sheet in A3?
Or are you saying the value in A3 on every sheet needs to be converted to it's value?

The value in A3 on every sheet needs to be converted to it's respective value.

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