macro is affecting sheets retroactively

Question

The macro is supposed to create a new sheet and name the sheet to the current date, copy the box from the previous sheet, paste it into the new sheet, and format the new box (new date/ clear contents).

The macro ran fine 7 times (it runs once a day) but today when I ran the macro, it retroactively changed the date of the cell (ex 12-17 sheet has 12/17 in cell) to the date in the sheet after. (So 12-17 sheet now has 12/18 in the cell, 12-20 sheet has 12/23 in cell, etc). However, it stops affecting the sheet that I started to use the macro on (12-13 is manual, 12-16 is macro created sheet. 12-13 is unaffected).

What's strange is that if I make a buffer sheet (Sheet2) and manually copy the box from 12-24 into it and run the macro, the sheet 12-26 comes out fine and changes 12/24 in Sheet2 to 12/26, but leaves sheets 12-24 and before unchanged.

This is the code for the macro:

    Sheets.Add(After:=ActiveSheet).Name = Format(Date, "MM-DD-YY")
    ActiveSheet.Previous.Select
    Cells(2, 2).Value = Format(Date, "MM/DD/YY")
    Range("B1:B2").HorizontalAlignment = xlCenter
    Range("A1:C21").Select
    Selection.Copy
    ActiveSheet.Next.Select
    ActiveSheet.Paste
    Columns("A:A").ColumnWidth = 20
    Columns("B:B").ColumnWidth = 25
    Columns("C:C").ColumnWidth = 20
    Range("C5:C7").ClearContents
    Range("C9:C10").ClearContents
    Range("C16:C18").ClearContents

End Sub

Thank you for your help.

Try to avoid select when it’s not strictly necessary. See https://stackoverflow.com/a/10717999/1521579 — Ricardo Diaz, Dec 26 '19 at 20:49

score 0 · Accepted Answer · answered Dec 26 '19 at 20:39

It looks like your third line is changing the date from the previous sheet.

Since it has the previous sheet selected, the third line goes to cell B2 and assigns the value of today's date to that cell in yesterday's sheet.

If you intend to make B2 today's date with that same formatting, I would move line 3 to somewhere after ActiveSheet.Paste, so your code would become:

Sheets.Add(After:=ActiveSheet).Name = Format(Date, "MM-DD-YY")
ActiveSheet.Previous.Select
Range("B1:B2").HorizontalAlignment = xlCenter
Range("A1:C21").Select
Selection.Copy
ActiveSheet.Next.Select
ActiveSheet.Paste
Cells(2, 2).Value = Format(Date, "MM/DD/YY")
Columns("A:A").ColumnWidth = 20
Columns("B:B").ColumnWidth = 25
Columns("C:C").ColumnWidth = 20
Range("C5:C7").ClearContents
Range("C9:C10").ClearContents
Range("C16:C18").ClearContents

macro is affecting sheets retroactively

1 Answers1