Update cell date on change in dynamic range

Microsoft Microsoft excel 2007 (pc)
May 11, 2010 at 09:35:48
Specs: Windows XP
Hello everyone.

I have an Excel worksheet detailing the status of invoices. Each invoice has its own column (range xx4:xx23). I want to have cell xx24 in each column include a date which changes when any cell in range xx4:xx23 is modified. So far I have this, which works:
Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("E4:E23")) Is Nothing Then

'Stop any possible runtime errors and halting code

On Error Resume Next

'Turn off ALL events so the Target * 2 does not _

'put the code into a loop.

Application.EnableEvents = False

Cells.Range("E24") = Format(Date, "dd-mmm-yy") & " at " & Format(Time, "hh:mm")

'Turn events back on

Application.EnableEvents = True

'Allow run time errors again

On Error GoTo 0

End If

End Sub

However, the number of columns in the worksheet is potentially infinite, and I want a date modified cell in each column, including new columns when they are added. What I don't want is to have to add a new bit of VBA each time I add a new column.

Is there a way of modifying my script so that it inserts the date modified in cell xx24 for each non-empty column when that column is modified? Or is there a way of adding to my 'copyColumn' script (below) so that it duplicates the 'last updated' script for that column at the time of inserting a new column?

Sub copyColumn()
Select Case Sheets("Sheet1").Range("A1") = ""
Case True 'paste in col A if A1 is empty
Sheets("Sheet1").Range("C4:C23").Copy
Sheets("Sheet1").Range("C4").PasteSpecial Paste:=xlPasteAll

Case False ' paste to next col
Sheets("Sheet1").Range("C4:C23").Copy
Sheets("Sheet1").Range("IV4").End(xlToLeft).Offset(0, 1).ColumnWidth = 19
Sheets("Sheet1").Range("IV4").End(xlToLeft).Offset(0, 1).PasteSpecial Paste:=xlPasteAll
End Select

Application.CutCopyMode = False
End Sub


See More: Update cell date on change in dynamic range

Report •


#1
May 11, 2010 at 13:32:27
The variable "Target" contains a lot of information about the cell that was changed, including it's Column address. Therefore you don't have to hardcode the Column designator, you can use Target.Column.

This should put your date/time string into Row 24 of the same column as the changed cell if the changed cell was within Range("E4:J23").

Adjust as required.

Private Sub Worksheet_Change(ByVal Target As Range)
'If change was made anywhere within E4:J23,
'Put date/time string in Row 24
  If Not Intersect(Target, Columns("E:J"), Rows("4:23")) Is Nothing Then
    Cells(24, Target.Column) = _
      Format(Date, "dd-mmm-yy") & " at " & Format(Time, "hh:mm")
  End If
End Sub


Report •

#2
May 11, 2010 at 14:35:46
P.S. What is your copyColumn code supposed to do?

As far as I can tell, if A1="", it pastes C4:C23 right on top of itself. Why not just Exit the sub if A1 is empty?


Report •

#3
May 11, 2010 at 15:32:55
Thank-you for your help on the 'last updated' issue - that seems to work a treat! :o)

To answer your other question - the CopyColumn script looks for the first empty column, copies the data from the desired range in an example (hidden) column and pastes it into the first empty column found. I found the code online. It may not be pretty, but it works :o)

Thanks again.


Report •

Related Solutions

#4
May 11, 2010 at 16:12:45
The copyColumn code only pastes what it copied into the first empty column if there is something in A1.

If A1 is empty, it just copies Range("C4:C23") right back on top of itself. I don't see any purpose for that.

The comments say:

'paste in col A if A1 is empty

bit in fact, the code says Copy C4:C23 and Paste it in C4:

Sheets("Sheet1").Range("C4:C23").Copy
Sheets("Sheet1").Range("C4").PasteSpecial     Paste:=xlPasteAll

All that does is paste what it copied right back on top of itself. Seems like a waste of resources to me.

Why not use:

Sub copyColumn()
 Select Case Sheets("Sheet1").Range("A1") = ""

  Case True 'Exit sub if A1 is empty
   Exit Sub

  Case False ' paste to next col
   Sheets("Sheet1").Range("C4:C23").Copy
   Sheets("Sheet1").Range("IV4").End(xlToLeft).Offset(0, 1).ColumnWidth = 19
   Sheets("Sheet1").Range("IV4").End(xlToLeft).Offset(0, 1).PasteSpecial _
      Paste:=xlPasteAll
End Select

 Application.CutCopyMode = False
End Sub


Report •

#5
May 12, 2010 at 09:49:36
Thank-you, I'll try that :)

Report •

Ask Question