Copy and Paste Value macro on multiple sheets

**Elmarie_Potgieter** · 05-16-2013, 11:55 AM

I have searched on the forum for an answer to my problem but cannot seem to find anything.

I have attached a example.

I have 3 sheets (GP, CT and EL), each sheet represent employees and hours. The amount of employees varies every month. I have formulas in row 1 which I would like to copy and paste value down for these employees. The formulas can be found in Row 1 columns D & E. I would like the formula to copy and paste value only to the last line which have a employee name in. The formula's in D1 and E1 I would like to keep so that I can use it to do spot checks.

I hope someone can help.

**JBeaucaire** · 05-16-2013, 08:31 PM

This should do it:

Option Explicit

Sub AddValuesFromFormulas()
Dim ws As Worksheet, LR As Long

For Each ws In Sheets(Array("GP", "CT", "EL"))
    LR = ws.Range("A" & Rows.Count).End(xlUp).Row
    With ws.Range("D4:E" & LR)
        ws.Range("D1:E1").Copy
        .PasteSpecial xlPasteFormulas
        .Value = .Value
    End With
Next ws

Application.CutCopyMode = False
End Sub

**Winon** · 05-17-2013, 12:24 AM

Hallo Elmarie_Potgieter,

Welkom by die Forum,

JBeaucaire gave you a very nice solution, and I thought that maybe you would need some help in putting it all together. If my assumption is correct, then please see the attached sample WorkBook.