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

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Watch MrExcel Video

Forum statistics

Threads
1,118,862
Messages
5,574,715
Members
412,613
Latest member
EFRATA
Top