Excel Macro That Changes Things when the date is changed?

**frahmed99** · 04-24-2013, 08:54 AM

Good day,

I have a bit of a problem. I have two worksheets. The first has two cells:
Book_1.png

I would like the Certificate No to change every time the date is changed. i.e if the user changes the month value from Mar-13 to Apr-13, i would like the certificate number to increase by 1. The certificate does not have the same value as the month. It could be any number.

The date also changes the columns in another sheet
Workbook_2.png

i.e all the values from the "Total to Date" column move to the column labeled "Previous". I am not an excel user and my employer wants me to do this.

. Any help will be appreciated.

Thanks in advance.

**JOHN H. DAVIS** · 04-24-2013, 11:50 AM

For your first problem maybe:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B35")) Is Nothing Then
    Target.Offset(2).Value = Target.Offset(2).Value + 1
End If
End Sub

**frahmed99** · 04-24-2013, 04:25 PM

Thank You. That has helped me out. But I am still stuck with the second part. Can someone please help me

**JOHN H. DAVIS** · 04-25-2013, 06:53 AM

Not sure I understand your layout on the second part, but see if this helps.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B35")) Is Nothing Then
    Target.Offset(2).Value = Target.Offset(2).Value + 1
    Sheets("Workbook2").Range("E12:E14").Cut Sheets("Workbook2").Range("C12:C14")
End If

End Sub

**frahmed99** · 04-25-2013, 08:17 AM

OMG. That is brilliant. Thank You very much. I was using the Select command:

Sheets ("Workbook2").Select
Range("E12:E14").Select
Selection.Cut
Range("C3:C27").Select
ActiveSheet.Paste

LOL. And it obviously didnt work. Thank you very much. You dont know how much you have helped me.

**JOHN H. DAVIS** · 04-25-2013, 08:22 AM

You're welcome. Glad to hear that it works for you. Thank you for the feedback, please comply with Forum Rule No. 9.

9. Acknowledge the responses you receive, good or bad. If your problem is solved, please say so clearly, and mark your thread as Solved: Click Thread Tools above your first post, select "Mark your thread as Solved". Or click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes. If more than two days have elapsed, the Dropdown option or Edit button will not appear -- ask a moderator to mark it.