Need Help With Finding Date And Copying Everything To The Right Of It?

Need Help With Finding Date And Copying Everything To The Right Of It?

Post by Jenny Marl » Sat, 19 Apr 2008 00:47:07


Hey Guys,

I am stuck on a problem and would really appreciate some help with it.
I heard this is a good place to look for help. =)

I have sequential dates in Cells C3:IV3, with correlating data for
that date below it.

I need help creating a macro that would look for the first day of the
current week in the range C3:IV3 and once it is found, would copy all
cells to the right of it and paste it into Sheet2. Is this possible?

For example, for today, it would look for the date 4/13 in the range
C3:IV3. It finds that date in cell X3. It then would need to copy
cells X3:IV75 from the current sheet1 into sheet2.

I am having a hard time coming up with the logic for this. I am not an
expert user but need to get this done!! Any help would be greatly
appreciated!!!!!!
 
 
 

Need Help With Finding Date And Copying Everything To The Right Of It?

Post by Dan R » Sat, 19 Apr 2008 01:12:56

Here's one way:

Sub test()
dt$ = Format(Int((Now - (Now - 2) Mod 7) - 1), "m/d/yyyy")
With Sheets(1)
Set found = .Range("C3:IV3").Find(What:=dt$, _

LookIn:=xlValues, _
Lookat:=xlWhole)
If Not found Is Nothing Then
lrow = .Cells(.Rows.Count, found.Column).Row
Set rng = .Range(.Cells(3, found.Column), .Cells(lrow, "IV"))
rng.Copy Sheets(2).Range("A3")
Else
MsgBox dt$ & " not found."
End If
End With
End Sub

Dan

 
 
 

Need Help With Finding Date And Copying Everything To The Right Of It?

Post by Dan R » Sat, 19 Apr 2008 01:16:50

Here's one way:

Sub test()
dt$ = Format(Int((Now - (Now - 2) Mod 7) - 1), "m/d/yyyy")
With Sheets(1)
Set found = .Range("C3:IV3").Find(What:=dt$, _
LookIn:=xlValues, _
Lookat:=xlWhole)
If Not found Is Nothing Then
lrow = .Cells(.Rows.Count, found.Column).End(xlUp).Row
Set rng = .Range(.Cells(3, found.Column), .Cells(lrow, "IV"))
rng.Copy Sheets(2).Range("A3")
Else
MsgBox dt$ & " not found."
End If
End With
End Sub

Dan
 
 
 

Need Help With Finding Date And Copying Everything To The Right Of It?

Post by Dave Peter » Sat, 19 Apr 2008 03:24:18

Check your other post, too.



--

Dave Peterson