Macro to paste special values [Closed]

Report
Posts
1
Registration date
Friday February 3, 2017
Status
Member
Last seen
February 4, 2017
-
Posts
2676
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 3, 2020
-
Hi All,

Am new to macro, i have an excell file in which column A is having dates with formula....for example cell A1 is stored with 01/1/2017 and cell A2 with 02/01/2017 and cell A3 03/01/2017 and cell A4 to A400 stored with 3/01/2017.Here i need a macro to paste special the values from cell A1 to A3 if cell A3 to A400 is having identical dates while closing the excel file.

Thanks in advance !

1 reply

Posts
2676
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 3, 2020
448
Hi Karthikha,

The code below will paste the formulas in A1:A3 as value in A1:A3 when all the cells in A4:A400 are the same as the value in A3. Workbook is automatically saved, so you might want to test this code on a copy of your workbook first.

Here is the code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)

If Application.WorksheetFunction.CountIf(Range("A4:A400"), Range("A3").Value) = 397 Then
    Range("A1:A3").Copy
    Range("A1:A3").PasteSpecial xlPasteValues
End If

ActiveWorkbook.Close SaveChanges:=True

End Sub


Best regards,
Trowa

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!