add a line of code using zn if statement or between

daniels012

Well-known Member
Joined
Jan 13, 2005
Messages
5,219
How can I write code to copy data from one sheet to another if the dates in a certain cell are between the begining date (cell F2) and Ending date (cell F3)

Thank you,
Michael
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
How's this for a start:

<font face=tahoma><SPAN style="color:#00007F">Sub</SPAN> DateRange()
    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Date</SPAN> >= [F2] And <SPAN style="color:#00007F">Date</SPAN> <= [F3] <SPAN style="color:#00007F">Then</SPAN>
        MsgBox <SPAN style="color:#00007F">Date</SPAN> & " is between your acceptable date range"
    Else: MsgBox Date & " is not between your acceptable date range"
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

Just replace "Date" with your qualifier.

Hope that helps,

Smitty
 

daniels012

Well-known Member
Joined
Jan 13, 2005
Messages
5,219
Here is what I have:

Dim c As Range, ws As Worksheet

Set ws = Worksheets("Running Total")

For Each c In ws.Range("K:K")
If c.Value >= [G2] And c.Value <= [G3] Then
c.EntireRow.Copy
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Select
End If

I need to copy the data from a defined name "propdata" in the Running Total worksheet and paste it into the new sheet.
Does my code above do this? It seems like it is just going to copy wherever it is at the moment???

Michael
 

daniels012

Well-known Member
Joined
Jan 13, 2005
Messages
5,219

ADVERTISEMENT

What am I missing?

Michael
 

daniels012

Well-known Member
Joined
Jan 13, 2005
Messages
5,219

ADVERTISEMENT

bump
 

XLGibbs

Well-known Member
Joined
Feb 25, 2005
Messages
2,446
You are asking us if the code works?

What happens when you run it?

Code:
Dim c As Range, ws As Worksheet
Dim x as Date
Set ws = Worksheets("Running Total")

For Each c In ws.Range("K:K")
x= Format(c.Value,"mm/dd/yyyy")

If x >= [G2] And x <= [G3] Then
c.EntireRow.Copy
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Select
End If

I would format the comparison to the same comparison as your G2 and G3 cells, and declare the variable passed as a DATE.

But you have not indicated if your code works or not, only if you want our opinion as to it's working or not.
 

XLGibbs

Well-known Member
Joined
Feb 25, 2005
Messages
2,446
I see you are struggling with two separate threads on the same topic.

I have PM'd you with my email address...you can send the file if you like.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,286
Messages
5,571,313
Members
412,382
Latest member
Langtn02
Top