Copy Paste all unlocked cells within work book with values over existing formula cells

MDK86

New Member
Joined
Oct 14, 2020
Messages
1
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Hi,

I have a workbook with multiple protected sheets.
What I want to do is run a Macro that will copy and paste all my unlocked cells to values.

I found on this forum someone answering this however it was just for a selection or column D.
I need the macro to run the whole work book.

Any help would be greatly appreciated.

Will

Sub ConvertUnlockedFormulasToValuesIfNextToBlankCells()

Dim FirstAddress As String, Cell As Range

Application.FindFormat.Clear

Application.FindFormat.Locked = False

Set Cell = Columns("D").Find("", SearchFormat:=True)

If Not Cell Is Nothing Then

FirstAddress = Cell.Address

Do

If Cell.Offset(, -1) = "" Then Cell.Value = Cell.Value

Set Cell = Columns("D").Find("", Cell, SearchFormat:=True)

Loop While Not Cell Is Nothing And Cell.Address <> FirstAddress

End If

Application.FindFormat.Clear

End Sub
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Watch MrExcel Video

Forum statistics

Threads
1,112,797
Messages
5,542,558
Members
410,560
Latest member
1ndependent
Top