How to copy row data from Sheet2 to Sheet1 based on Conditional Date


J

John Lauer

Developing using Excel 2007, but need to be compatible with 2003.

Problem:

Workbook has two sheets. Sheet two contains data, columns A thru M.
Column C is formatted for Date values. Not all rows contain a value in
column C. Data begins in row 2.

Sheet One has 3 'Option Buttons (form Control), labeled Contract date,
Effective Date, and End Date. When contract date is selected, Need
data on sheet two, column C (Date is contained here) to be queried
with a conditional filter... If date < today's date + 14 days ... If
true, copy column C thru M of that row to Sheet One beginning at cell
C13. Continue until all data rows have been tested.

If another 'Option Button' is selected, results from first query are
replaced by results from second query.
 
Ad

Advertisements

J

John Lauer

Here is the code that I have been working on... But I can not get it
to work..

Sub OptionButton1_Click()

TEMPLATE_SHEET = "Data_Input"
Database_sheet = "Carrier"
Application.ScreenUpdating = False

myzerorange = "C" & ActiveWindow.RangeSelection.Row & ":" & "M" &
ActiveWindow.RangeSelection.Row
mycompany = "C" & ActiveWindow.RangeSelection.Row
mydate = "D" & ActiveWindow.RangeSelection.Row
Database_sheet = ActiveSheet.Name
DATABASE_RECORDS = Sheets(Database_sheet).Range("C2:C1000")
Count_Row = 13

If Range(mycompany) <> "" Then
If Range(mydate) <> "" Then
'Range(mydate) = contractdate
If mydate < DateAdd("d", 14, "Today()") Then
Range(myzerorange).Copy
Sheets(TEMPLATE_SHEET).Select

'To identify the next blank row in the database sheet

DATABASE_RECORDS =
Sheets(TEMPLATE_SHEET).Range("C13:C1000")
'To identify the next blank row in the data_Input
sheet
For Each DBRECORD In DATABASE_RECORDS
If DBRECORD <> "" Then Count_Row = Count_Row + 1
Next DBRECORD

Sheets(TEMPLATE_SHEET).Range("C" & Count_Row).Select
ActiveSheet.Paste

'Return to origin and check for another contract date
Sheets(Database_sheet).Select
Else

End If
Else

End If
End If
Application.ScreenUpdating = True

End Sub
 
Ad

Advertisements

D

Don Guillett

Developing using Excel 2007, but need to be compatible with 2003.

Problem:

Workbook has two sheets. Sheet two contains data, columns A thru M.
Column C is formatted for Date values. Not all rows contain a value in
column C. Data begins in row 2.

Sheet One has 3 'Option Buttons (form Control), labeled Contract date,
Effective Date, and End Date. When contract date is selected, Need
data on sheet two, column C (Date is contained here) to be queried
with a conditional filter... If date < today's date + 14 days ... If
true, copy column C thru M of that row to Sheet One beginning at cell
C13. Continue until all data rows have been tested.

If another 'Option Button' is selected, results from first query are
replaced by results from second query.
"If desired, send your file to dguillett1 @gmail.com I will only look
if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results."
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top