none
Macro if then for specific dates RRS feed

  • Question

  • Sub Macro1()
    ' Macro1
        If Date >= DateAdd(DateInterval.Month, 1, "AP4") Then
        Columns("AT:AT").Select
        Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
        Range("AP4:AP9").Select
        Selection.Copy
        Range("AT4").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Application.CutCopyMode = False
        Range("B12").Select
        End If
    End Sub

    I am trying to only allow this macro to work after the each month. Cell AP4 is the previous month that has been updated. It is just giving errors and I'm not totally sure how to rectify them.

    Thank you for any help

    Monday, January 18, 2016 10:51 AM

Answers

  • Sub Macro1()
      ' Macro1
      If Month(Date) > Month(Range("AP4")) Then
        Columns("AT:AT").Insert
        Range("AP4:AP9").Copy
        Range("AT4").PasteSpecial Paste:=xlPasteValues
      End If
    End Sub


    EDIT:

    If Month(Date) <> Month(Range("AP4")) Then

    might be better if the last month is in December

    Monday, January 18, 2016 11:13 AM
  • Hi gretse,

    Have your issue been resolved? I suggest you try the code below:

    Sub Macro1()
    ' Macro1
        Dim mydate As Date
        mydate = Range("AP4").Value
        If Date >= DateAdd("m", 1, mydate) Then
        Columns("AT:AT").Select
        Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
        Range("AP4:AP9").Select
        Selection.Copy
        Range("AT4").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Application.CutCopyMode = False
        Range("B12").Select
        End If
    End Sub

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Wednesday, January 20, 2016 6:25 AM

All replies

  • Sub Macro1()
      ' Macro1
      If Month(Date) > Month(Range("AP4")) Then
        Columns("AT:AT").Insert
        Range("AP4:AP9").Copy
        Range("AT4").PasteSpecial Paste:=xlPasteValues
      End If
    End Sub


    EDIT:

    If Month(Date) <> Month(Range("AP4")) Then

    might be better if the last month is in December

    Monday, January 18, 2016 11:13 AM
  • Hi,

    This is the forum to discuss questions and feedback for Microsoft Excel, I'll move your question to the MSDN forum for Excel

    http://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev&filter=alltypes&sort=lastpostdesc

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    Regards,

    Emi Zhang
    TechNet Community Support


    Please mark the reply as an answer if you find it is helpful.

    If you have feedback for TechNet Support, contact tnmff@microsoft.com.
    Tuesday, January 19, 2016 2:21 AM
  • Hi gretse,

    Have your issue been resolved? I suggest you try the code below:

    Sub Macro1()
    ' Macro1
        Dim mydate As Date
        mydate = Range("AP4").Value
        If Date >= DateAdd("m", 1, mydate) Then
        Columns("AT:AT").Select
        Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
        Range("AP4:AP9").Select
        Selection.Copy
        Range("AT4").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Application.CutCopyMode = False
        Range("B12").Select
        End If
    End Sub

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Wednesday, January 20, 2016 6:25 AM