Hi all, I write an VBA that need to copy a filtered data from sheet a to sheet b, I want to add the timestamp to the right column on sheet b. However, the vba only add 1row time stamp for each paste action. The timestamp cannot past till to the last row.
Below are part of my VBA code
How can I fill the timestamp to each row when I copy the visible data from sheet A to sheet B? Many Thanks.
Below are part of my VBA code
VBA Code:
Sub Transpose()
Dim whs, whs2 As Worksheet
Dim lastrow2, lastrow3 As Long
Dim i As Integer
Set whs = ThisWorkbook.Worksheets("sheet1")
Set whs2 = ThisWorkbook.Worksheets("sheet2")
lastrow2 = whs2.Range("D" & whs2.Rows.Count).End(xlUp).Row
lastrow3 = wrs.Range("B" & wrs.Rows.Count).End(xlUp).Row
whs.Activate
If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData
wrs.Range("J6").AutoFilter field:=10, Criteria1:="<>", Operator:=xlFilterValues
wrs.Range("B7:C" & lastrow3 & "," & "I7:K" & lastrow3).SpecialCells(xlCellTypeVisible).Copy Destination:=rcod.Cells(lastrow2 + 1, 4)
For i = 2 To Lastrow
If rcod.Cells(i, "D").Value <> "" And rcod.Cells(i, "E").Value <> "" And rcod.Cells(i, "F").Value <> "" And rcod.Cells(i, "G").Value <> "" And rcod.Cells(i, "H").Value <> "" Then
rcod.Cells(i + 1, "I").Value = Date
rcod.Cells(i + 1, "I").NumberFormat = "mm/dd/yy"
End If
Next i
Application.CutCopyMode = False
End Sub
How can I fill the timestamp to each row when I copy the visible data from sheet A to sheet B? Many Thanks.