VBA Time/date stamp when copy/pasting values

[tpowell1986]

hi,

i need of some help please, i have a simple script that is copying values from a workbook to the next empty row on another tab, i would like to have a time and date stamp in column B showing when the information was pasted.

thanks in advance

Range("t2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets("invoice").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

 

[Fluff]

How about

Range("t2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
With Sheets("invoice").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
   .PasteSpecial xlPasteValues
   .Offset(, 1).Resize(Selection.Rows).Value = Now
End With

 

[tpowell1986]

How about

Range("t2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
With Sheets("invoice").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
   .PasteSpecial xlPasteValues
   .Offset(, 1).Resize(Selection.Rows).Value = Now
End With

thanks for the reply, it debugs on this line? .Offset(, 1).Resize(Selection.Rows).Value = Now

 

[Fluff]

It works for me, what error do you get?

 

[tpowell1986]

Hi, error attached, when you debug it stops on the .Offset(, 1).Resize(Selection.Rows).Value = Now line, column A has copied but nothing in column B when it stops

 

[Fluff]

What happens if you use

VBA.Now

 

[tpowell1986]

Sub Macro1()
'
' Macro1 Macro
'

'
Sheets("Alliance").Select
ActiveSheet.ListObjects("Query1_2").Range.AutoFilter Field:=21, Criteria1:= _
"Yes"
Range("t2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets("invoice").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Sheets("Alliance").Select
ActiveWorkbook.Worksheets("Alliance").ListObjects("Query1_2").Sort.SortFields. _
Clear
ActiveSheet.ShowAllData
End Sub

 

[Fluff]

That doesn't include the suggestion that I made.

 

[tpowell1986]

sorry i had not saved the file and closed it, code would be

Sub Macro1()
'
' Macro1 Macro
'

'
Sheets("Alliance").Select
ActiveSheet.ListObjects("Query1_2").Range.AutoFilter Field:=21, Criteria1:= _
"Yes"

Range("t2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
With Sheets("invoice").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
.PasteSpecial xlPasteValues
.Offset(, 1).Resize(Selection.Rows).Value = Now
End With

Sheets("Alliance").Select
ActiveWorkbook.Worksheets("Alliance").ListObjects("Query1_2").Sort.SortFields. _
Clear
ActiveSheet.ShowAllData
End Sub

 

[Fluff]

Try what I suggested in post#6