VBA code relative to current date

[Jbm]

Hi,
I'm working with Excel 2007, and every day I receive a new automatically
generated worksheet with data from every day of that month up to and
including the current day. So column A contains the date, looks something
like:
7/01/2009
7/01/2009
7/01/2009
7/02/2009
7/02/2009
etc.
Suppose that I get the worksheet for 7/29/2009, in which case I need to pull
out data only from 7/28/2009. Furthermore I only need to pull data from that
date that contains certain keywords, like "wood" and "tile." The current
solution I can think of would be to either pull all the data by keyword, and
then run something that eliminates any of that data that isn't the desired
date. Or, there may be a way to look at the last non empty cell in A,
subtract 1 from that date and then use that new date as the criteria for what
data the macro pulls.
The current macro I built pulls the data by keyword, but not by date, so
maybe either modify this macro or write a new, simpler one?

Sub DataMove()
RowCount = 1
For Each c In Range("B:B")
If c.Value Like "*Wood*" Or _
c.Value Like "*Stone*" Or _
c.Value Like "*Tile*" Then
Cells(RowCount, "H").Value = c.Value
Cells(RowCount, "I").Value = c.Offset(0, 1).Value
Cells(RowCount, "J").Value = c.Offset(0, 2).Value
Cells(RowCount, "K").Value = c.Offset(0, 3).Value
Cells(RowCount, "G").Value = c.Offset(0, -1).Value
RowCount = RowCount + 1
End If
Next
End Sub

 

[Joel]

Sub DataMove()

set SrcSht = Sheets("Sheet1")
set DestSht = Sheets("Sheet2")

NewRow = 1
RowCount = 1

with SrcSht
Do while .range("B" & RowCount) <> ""
Keyword = .range("B" & RowCount)
if Keyword = "wood" or _
Keyword = "stone" or _
Keywood = "tile" then

DestSht.Cells(NewRow, "H").Value = .Cells(NewRow, "A")
DestSht.Cells(NewRow, "J").Value = .Cells(NewRow, "B")
DestSht.Cells(NewRow, "K").Value = .Cells(NewRow, "C")
DestSht.Cells(NewRow, "G").Value = .Cells(NewRow, "D")
NewRow = NewRow, + 1
end if
RowCount = RowCount + 1
Loop
end with
End Sub

 

[Jbm]

Joel,
Thanks for spending time helping me on this, it's much appreciated. I'm
running into a few issues with your code though. Minorly, in case you try to
test the code, note that there's an extra comma on New Row = New Row + 1.
Onto the real troubles I'm having though. First, the code doesn't seem to
want to grab the correct rows of Keywords with the "if Keyword" strings. For
some reason the old "if c.Value Like" grabbed the right ones, but not these,
so maybe we should use the old code on that? Another less pressing problem
is that the new code is mixing up what order the data is in. I think I
should have been more clear in the original description: columns A-E have
data in them, A holds the date, B holds the name with the words we're
searching for, and C-E have numerical data. The new code however is placing
the data into columns in the order DA Blank BC, and E doesn't even show up.
More important than these issues however is that the new code does pull
specific dates, but not the ones we need. I used your code on a worksheet
that goes up to 7/23/09, but the data pulled only consisted of dates 7/01/09
to 7/03/09. For that sheet, for example, I am trying to get data only from
7/22/09.

I guess I didn't realize how difficult this might be, and apologies if I
wasn't clear at the start -- if you need more clarification to help, please
just ask, any way I can help you, helps me. Thanks for the help again,
though.

 

[Joel]

I fixed the row problem and the comma. the order can be changed to any order
you want. I gave just an example. Put in as many columns of data that you
want to move and the source and desttination column as required.

I don't know what column the dat is located so I can't help with filtering
the date automatically.

Sub DataMove()

Set SrcSht = Sheets("Sheet1")
Set DestSht = Sheets("Sheet2")

NewRow = 1
RowCount = 1

With SrcSht
Do While .Range("B" & RowCount) <> ""
Keyword = .Range("B" & RowCount)
If Keyword = "wood" Or _
Keyword = "stone" Or _
Keywood = "tile" Then

DestSht.Cells(NewRow, "H").Value = .Cells(RowCount, "A")
DestSht.Cells(NewRow, "J").Value = .Cells(RowCount, "B")
DestSht.Cells(NewRow, "K").Value = .Cells(RowCount, "C")
DestSht.Cells(NewRow, "G").Value = .Cells(RowCount, "D")
NewRow = NewRow + 1
End If
RowCount = RowCount + 1
Loop
End With
End Sub

 

[Jbm]

Joel,
I'm afraid I'm being counterproductive here. Let my try to simplify this.
Don't worry about keywords for this example at all.
Imagine I have the dates in column A like this, with corresponding data in
column B

Column A
7/01/09
7/01/09
7/02/09
7/02/09
7/02/09
7/03/09
7/03/09

Now suppose the date is 7/03/09, so in this instance I need to copy rows 3-5
(data from the previous day) and place that data in Columns D and E. So
Column D would look like this

Column D
7/02/09
7/02/09
7/02/09

And Column E would have the corresponding data to those dates that was in B.
I need to do this every day, with a separate new document, so for today (the
29th), I need Column D to hold only data from the 28th. The last cell in A
will always contain the current date. I'm sorry to have wasted your effort
on the previous code, but I think this will get at what I'm trying to do with
better accuracy. Thanks.

 

[Joel]

I added an inport box to select the rows you want. I also added a new workbook

Sub DataMove()

Set SrcSht = Sheets("Sheet1")

Set Newbk = Workbooks.Add
Set DestSht = Newbk.Sheets("Sheet1")

SrcSht.Activate
Set MyCell = Application.InputBox( _
prompt:="Select Row to Export", _
Type:=8)

Set SrcSht = MyCell.Parent
FirstRow = MyCell.Row
LastRow = FirstRow + MyCell.Rows.Count - 1

NewRow = 1
RowCount = 1

With SrcSht
For RowCount = FirstRow To LastRow
Keyword = .Range("B" & RowCount)
If Keyword = "wood" Or _
Keyword = "stone" Or _
Keywood = "tile" Then

DestSht.Cells(NewRow, "H").Value = .Cells(RowCount, "A")
DestSht.Cells(NewRow, "J").Value = .Cells(RowCount, "B")
DestSht.Cells(NewRow, "K").Value = .Cells(RowCount, "C")
DestSht.Cells(NewRow, "G").Value = .Cells(RowCount, "D")
NewRow = NewRow + 1
End If
RowCount = RowCount + 1
Next RowCount
End With
End Sub