Macro to paste special values
Closed
karthikha
Posts
1
Registration date
Friday February 3, 2017
Status
Member
Last seen
February 4, 2017
-
Updated by Ambucias on 4/02/17 at 04:33 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Feb 6, 2017 at 11:40 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Feb 6, 2017 at 11:40 AM
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 !
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 !
Related:
- Macro to paste special values
- Special character letter - Guide
- How to type special e - Guide
- Spell number in excel without macro - Guide
- How to paste photo in resume - Guide
- Excel macro to create new sheet based on value in cells - Guide
1 response
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
555
Feb 6, 2017 at 11:40 AM
Feb 6, 2017 at 11:40 AM
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:
Best regards,
Trowa
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