Hi,
I'm looking for some help with VBA to test dates in the H column of multiple
worksheets (H column has varying lengths). If the date is within the next
week, I would like to copy the row to a seperate worksheet. In essence, I
would like to create a week lookahead report. I'm able to work this out with
functions etc, but would like to learn how to do it VBA-style. Thanks!

Give this a try:

Public Sub test()
Dim StartRow As Long
Dim EndRow As Long

With ActiveSheet

On Error Resume Next
StartRow = Application.Match(CLng(.Range("A1").Value), .Range("rngDates"), 0)
On Error GoTo 0

If StartRow = 0 Then

MsgBox "Start date not matched"
Exit Sub
End If

On Error Resume Next
EndRow = Application.Match(CLng(.Range("B1").Value), .Range("rngDates"), 0)
On Error GoTo 0

If EndRow = 0 Then

MsgBox "End date not matched"
Exit Sub
End If

..Range("rngDates").Cells(StartRow, 1).Resize(EndRow - StartRow +
1).EntireRow.Copy _
Worksheets("Sheet2").Range("A1")
End With
End Sub

The macro copies an entire row, if a date in column A falls between two
dates, which are in CellsA1 (today) and B1 (today + 7).

HTH,
Ryan---



--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"MSchmidty2" wrote:

Hi,
I'm looking for some help with VBA to test dates in the H column of multiple
worksheets (H column has varying lengths). If the date is within the next
week, I would like to copy the row to a seperate worksheet. In essence, I
would like to create a week lookahead report. I'm able to work this out with
functions etc, but would like to learn how to do it VBA-style. Thanks!

Coupe of assumptions in the following code. You're starting in A1 and there
is data in each cell in Column A. I'm a bit unsure what you mean by Column H
having varying lengths - do you mean format? Anyways, maybe this will help

Do Until Activecell = ""
DataSheet = ActiveSheet.Name
MyDate = Format(Activecell(1,8),"dd/mm/yyyy")
MyToday = Format(Now(),"dd/mm/yyyy")
MyFuture = Format(MyToday + 7,"dd/mm/yyyy")
If MyDate < MyFuture AND MyDate MyToday Then
Activecell.EntireRow.Copy
Sheets("Next Week").Select 'Or whatever sheet it is that you're using
ActiveSheet.Paste
Sheets(DataSheet).Select
Application.CutCopyMode = False
End If
Activecell(2,1).Select
Loop




"MSchmidty2" wrote:

Hi,
I'm looking for some help with VBA to test dates in the H column of multiple
worksheets (H column has varying lengths). If the date is within the next
week, I would like to copy the row to a seperate worksheet. In essence, I
would like to create a week lookahead report. I'm able to work this out with
functions etc, but would like to learn how to do it VBA-style. Thanks!

LOFE,
I mean that each worksheet has a varying number of rows of information. The
information starts at A5 on each sheet. Where would I place this code to
attempt to run it? I expected to place it in ThisWorkbook, but nothing
happened.

Thanks,
Mike

"LOFE" wrote:

Coupe of assumptions in the following code. You're starting in A1 and there
is data in each cell in Column A. I'm a bit unsure what you mean by Column H
having varying lengths - do you mean format? Anyways, maybe this will help

Do Until Activecell = ""
DataSheet = ActiveSheet.Name
MyDate = Format(Activecell(1,8),"dd/mm/yyyy")
MyToday = Format(Now(),"dd/mm/yyyy")
MyFuture = Format(MyToday + 7,"dd/mm/yyyy")
If MyDate < MyFuture AND MyDate MyToday Then
Activecell.EntireRow.Copy
Sheets("Next Week").Select 'Or whatever sheet it is that you're using
ActiveSheet.Paste
Sheets(DataSheet).Select
Application.CutCopyMode = False
End If
Activecell(2,1).Select
Loop




"MSchmidty2" wrote:

Hi,
I'm looking for some help with VBA to test dates in the H column of multiple
worksheets (H column has varying lengths). If the date is within the next
week, I would like to copy the row to a seperate worksheet. In essence, I
would like to create a week lookahead report. I'm able to work this out with
functions etc, but would like to learn how to do it VBA-style. Thanks!