VB code donation required please!

[Guest]

Can you help a novice and provide some vb code to do the following...

in worksheet1 (log) column A contains an unlimited number of dates
columns B:Y contain other data.
I want a search made for the whole of column A for 'todays' date and each
time a match is found to copy that particular rows data (cells B:Y) into
worksheet2 (print). This loop is continued until the whole of column A has
been searched
so basicaly a list of data found matching todays date is pasted to another
sheet.

easy uh !

thanks in advancve

 

[Don Guillett]

we learn by doing so record a macro while you do this. Then come back for
more help

sort by date>copy>paste>

 

[Guest]

Don,
thanks for your reply,
but how do you 'tell' a recoding macro that the dates listed in column A are
to be matched , ie if my lcolumn has aprox 1000 lines of dates, of which 150
have todays date, how do I get the macro (whilst being recorded) to do this.

 

[Bob Phillips]

Sub Test()
Dim iLastRow As Long
Dim i As Long
Dim j As Long

With Worksheets("Log")
iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 1 To iLastRow
If .Cells(i, "A").Value = Date Then
j = j + 1
.Cells(i, "A").Resize(, 24).Copy _
Worksheets("print").Cells(j, "A")
End If
Next i
End With

End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Tom Ogilvy]

Sub ab()
With Worksheets("Log")
If Application.CountIf(.Columns(1), _
Format(Date, "mm/dd/yyyy")) > 0 Then
.Range("A1").CurrentRegion.AutoFilter _
Field:=1, Criteria1:="=" & Date
With .AutoFilter.Range
.Offset(1, 1).Resize( _
.Rows.Count - 1, 24).Copy _
Destination:=Worksheets( _
"Print").Range("A2")
.AutoFilter
End With
End If
End With

End Sub

would be a start. Assumes data table on Log starts in A1.

 

[Guest]

Bob,thanks
I input the code and got a
Runtime error 1004
Application-defined or object defined error

uh !!!!!

 

[Bob Phillips]

Anthony,

It works for me okay, although I do notice one logic error in there, but not
one that causes a Runtime error 1004

Sub Test()
Dim iLastRow As Long
Dim i As Long
Dim j As Long

With Worksheets("Log")
iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 1 To iLastRow
If .Cells(i, "A").Value = Date Then
j = j + 1
.Cells(i, "B").Resize(, 24).Copy _
Worksheets("print").Cells(j, "A")
End If
Next i
End With

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Rowan Drummond]

Check your sheet names. They must be Log and print (case sensitive.)

Hope this helps
Rowan

 

[Chip Pearson]

Sheet names are not case-sensitive.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Rowan Drummond]

Thanks Chip. Of course they aren't. Don't know what I was thinking.

Regards
Rowan