Hi I am currently using the code below to find multiple values and the copy/paste into another sheet. What I would like to know is if it is possible to add the now date to a column in sheet 1 for the value found in sheet 3?
Option Explicit
Sub BBIB()
Dim srchLen, gName, nxtRw As Integer
Dim g As Range
'Clear Sheet 2 and Copy Column Headings
Sheets(2).Cells.ClearContents
Sheets(1).Rows(1).Copy Destination:=Sheets(2).Rows(1)
'Determine length of Search Column from Sheet3
srchLen = Sheets(3).Range("A" & Rows.Count).End(xlUp).Row
'Loop through list in Sheet3, Column A. As each value is
'found in Sheet1, Column A, copy it top the next row in Sheet2
With Sheets(1).Columns("A")
For gName = 2 To srchLen
Set g = .Find(Sheets(3).Range("A" & gName), lookat:=xlWhole)
If Not g Is Nothing Then
nxtRw = Sheets(2).Range("A" & Rows.Count).End(xlUp).Row + 1
g.EntireRow.Copy Destination:=Sheets(2).Range("A" & nxtRw)
End If
Next
End With
End SubAny help much appreciated.
Hi! Please click on the blue line at the end of this post and read the instructions on how to post VBA code in this forum. Then please repost your code, as copied from the VBE, so that it is easier for us to read.
Thanks!
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.
I am confused as to where you want the date on Sheet(1). So here is the code I would have used: Add this to what you have at the top of your code:
Dim nowdate As Date nowdate = DateValue(Now)Now you can use 'nowdate' wherever you'd like. For example, to test the code, I just had it pop up in a message box.
MsgBox("Today's Date Test: " & nowdate)Hope this helps.
Law of Logical Argument: Anything is possible if you don't know what you're talking about.
message edited by Newbie10
That looks like more work than is required. There's no need to create another variable nor extract the Date from Now. Just adding this line inside the If-Then loop will accomplish the goal. (I randomly choose Column B for the Date)
Sheets(1).Cells(g.Row, "B") = DateHowever, I'd still like to see the code posted in the requested manner. (It's a teaching moment.)
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.
Yes (14) | ![]() | |
No (14) | ![]() | |
I don't know (15) | ![]() |