Copy specific cells in row if range of columns contains a date

Jessica - Mar 25, 2016 at 05:23 PM

I have a macro set up that:
1. asks for a start date and end date
2. scans a specified range of columns for a date within the given range
3. copies the cell to the right of any cell containing a valid date and pastes the content onto a different sheet

What I NEED it to do is copy a specific set of cells within the row containing the date, not just the cell to the right of the cell containing the valid date.
We use this file to keep track of release dates/quantities of certain items for our customers, so the purpose of this would be to scan the cells for upcoming release dates and produce the cells containing the order#, item#, quantity, shipping method, and date of release for any row containing a valid date.

Below is the macro. Within the row containing the valid date I need it to copy the cell in column B, E, G, H, the cell containing the date itself, and the cell directly to the right of the date cell, and paste the results onto a different sheet.

Sub SanDiego_Releases()

Dim startdate As Date, enddate As Date
Dim rng As Range, destRow As Long
Dim shtSrc As Worksheet, shtDest As Worksheet
Dim c As Range

Set shtSrc = Sheets("San Diego")
Set shtDest = Sheets("RELEASE SCHEDULE")

destRow = 3 'start copying to this row

startdate = CDate(InputBox("Beginning Date"))
enddate = CDate(InputBox("End Date"))

'don't scan the entire column...
Set rng = Application.Intersect(shtSrc.Range("K:Z"), shtSrc.UsedRange)

For Each c In rng.Cells
If c.Value >= startdate And c.Value <= enddate Then

c.Offset(0, 1).Resize(1, 5).Copy _
shtDest.Cells(destRow, 4)

destRow = destRow + 1

End If

End Sub

I've been trying to fix this myself but I can't find an explanation for the c.Offset(0, 1).Resize(1, 5).Copy _ part of the macro so I don't know how to change the cells that get copy/pasted.

Thanks for any help!