1

I have a macro that I am running to paste data into an excelsheet. When I paste the data I want to add the time I did that so I can see when I last updated the data. I found an easy solution for that:

[F2] = Now

looks simple and works like a charm. But then there's a problem. This only works if the cell I want the data in is on the same tab as I am running the macro from, but i want that timestamp on another tab. I tried to change the "Overview"tab and back to "Orders" but it doesnt work:

Sheets("Overview").Select
[F2] = Now
Sheets("Orders").Select

It just gives a 400 error with no explanation. Does anybody know the solution?

Vityata
  • 42,633
  • 8
  • 55
  • 100
perbrethil
  • 131
  • 1
  • 12
  • 1
    A cell reference with no clearly defined parent worksheet on a worksheet's private code sheet (not a public module code sheet) will always default to that worksheet. Activating another worksheet does nothing to change this. –  Jun 05 '18 at 10:01
  • @Jeeped - actually this should be the reason why it did not work in first place, the code is most probably in a `Worksheet`, not in a `Module`. – Vityata Jun 05 '18 at 10:03
  • 1
    @Vityata - yes, timestamps are typically produced with a worksheet_change event sub procedure. –  Jun 05 '18 at 10:04
  • If you really want to use the evaluation shortcut `[...] `, you can explicitly tell the sheet using `[Overview!F2]` – Vincent G Jun 05 '18 at 10:05
  • @Jeeped - I usually produce them in a log sheet, every time I run something... – Vityata Jun 05 '18 at 10:05
  • @perb - `[`Shortcuts`']` should not be used in production, and especially not when troubleshooting a problem. The goal is to remove potential issues, not to add more. – ashleedawg Jun 05 '18 at 10:31

1 Answers1

2

Ignore the Select completely, it is not needed. Probably the error is somewhere in the logic of the other code. However, this should work:

Worksheets("Overview").Range("F2") = Now

or with different syntactic sugar:

Worksheets("Overview").[F2] = Now

How to avoid using Select in Excel VBA

Vityata
  • 42,633
  • 8
  • 55
  • 100