I want the code to pick dates from column Q (see line 4) add 7 days and put
the new dates into column k.

Any suggestions?

Sub copy_Dates()
Sheets(2).Range("e4:e65536").Value = Sheets(1).Range("d4:d65536")
Sheets(1).Range("n4:Q65536").Value = Sheets(2).Range("f4:i65536").Value
Sheets(1).Range("k4:K65536").Value = Range("q4:q65536").Value
Sheets(1).Select
Range("k65536").End(xlUp).Activate
If ActiveCell.Value <= Now - 30 Then
msgbox "NICE!!"
Else: Exit Sub
End If
End Sub

Hi,

Use the DateAdd method. This adds 7 days to a date in Col Q and puts it in
Col K

Sub Copy_Dates()
Set sht = Sheets(1)
Dim LastRow As Long
Dim c As Range
LastRow = sht.Cells(Cells.Rows.Count, "Q").End(xlUp).Row
Set MyRange = sht.Range("Q4:Q" & LastRow)
For Each c In MyRange
If IsDate(c) Then
c.Offset(, -6).Value = DateAdd("d", 7, c.Value)
End If
Next
End Sub

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"tomjoe" wrote:

> I want the code to pick dates from column Q (see line 4) add 7 days and put
> the new dates into column k.
>
> Any suggestions?
>
> Sub copy_Dates()
> Sheets(2).Range("e4:e65536").Value = Sheets(1).Range("d4:d65536")
> Sheets(1).Range("n4:Q65536").Value = Sheets(2).Range("f4:i65536").Value
> Sheets(1).Range("k4:K65536").Value = Range("q4:q65536").Value
> Sheets(1).Select
> Range("k65536").End(xlUp).Activate
> If ActiveCell.Value <= Now - 30 Then
> msgbox "NICE!!"
> Else: Exit Sub
> End If
> End Sub
>

Hi Mike
Worked like a charm.
Awsome simple and terrible smart.
Thank you so much.


Mike H skrev:

> Hi,
>
> Use the DateAdd method. This adds 7 days to a date in Col Q and puts it in
> Col K
>
> Sub Copy_Dates()
> Set sht = Sheets(1)
> Dim LastRow As Long
> Dim c As Range
> LastRow = sht.Cells(Cells.Rows.Count, "Q").End(xlUp).Row
> Set MyRange = sht.Range("Q4:Q" & LastRow)
> For Each c In MyRange
> If IsDate(c) Then
> c.Offset(, -6).Value = DateAdd("d", 7, c.Value)
> End If
> Next
> End Sub
>
> --
> Mike
>
> When competing hypotheses are otherwise equal, adopt the hypothesis that
> introduces the fewest assumptions while still sufficiently answering the
> question.
>
>
> "tomjoe" wrote:
>
> > I want the code to pick dates from column Q (see line 4) add 7 days and put
> > the new dates into column k.
> >
> > Any suggestions?
> >
> > Sub copy_Dates()
> > Sheets(2).Range("e4:e65536").Value = Sheets(1).Range("d4:d65536")
> > Sheets(1).Range("n4:Q65536").Value = Sheets(2).Range("f4:i65536").Value
> > Sheets(1).Range("k4:K65536").Value = Range("q4:q65536").Value
> > Sheets(1).Select
> > Range("k65536").End(xlUp).Activate
> > If ActiveCell.Value <= Now - 30 Then
> > msgbox "NICE!!"
> > Else: Exit Sub
> > End If
> > End Sub
> >

Glad i could help and thanks for the feedback
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"tomjoe" wrote:

> Hi Mike
> Worked like a charm.
> Awsome simple and terrible smart.
> Thank you so much.
>
>
> Mike H skrev:
>
> > Hi,
> >
> > Use the DateAdd method. This adds 7 days to a date in Col Q and puts it in
> > Col K
> >
> > Sub Copy_Dates()
> > Set sht = Sheets(1)
> > Dim LastRow As Long
> > Dim c As Range
> > LastRow = sht.Cells(Cells.Rows.Count, "Q").End(xlUp).Row
> > Set MyRange = sht.Range("Q4:Q" & LastRow)
> > For Each c In MyRange
> > If IsDate(c) Then
> > c.Offset(, -6).Value = DateAdd("d", 7, c.Value)
> > End If
> > Next
> > End Sub
> >
> > --
> > Mike
> >
> > When competing hypotheses are otherwise equal, adopt the hypothesis that
> > introduces the fewest assumptions while still sufficiently answering the
> > question.
> >
> >
> > "tomjoe" wrote:
> >
> > > I want the code to pick dates from column Q (see line 4) add 7 days and put
> > > the new dates into column k.
> > >
> > > Any suggestions?
> > >
> > > Sub copy_Dates()
> > > Sheets(2).Range("e4:e65536").Value = Sheets(1).Range("d4:d65536")
> > > Sheets(1).Range("n4:Q65536").Value = Sheets(2).Range("f4:i65536").Value
> > > Sheets(1).Range("k4:K65536").Value = Range("q4:q65536").Value
> > > Sheets(1).Select
> > > Range("k65536").End(xlUp).Activate
> > > If ActiveCell.Value <= Now - 30 Then
> > > msgbox "NICE!!"
> > > Else: Exit Sub
> > > End If
> > > End Sub
> > >

Or just add 7 (since you're adding days):

If IsDate(c) Then
with c.Offset(, -6)
.Value = .value + 7
end with
End If

Mike H wrote:
>
> Hi,
>
> Use the DateAdd method. This adds 7 days to a date in Col Q and puts it in
> Col K
>
> Sub Copy_Dates()
> Set sht = Sheets(1)
> Dim LastRow As Long
> Dim c As Range
> LastRow = sht.Cells(Cells.Rows.Count, "Q").End(xlUp).Row
> Set MyRange = sht.Range("Q4:Q" & LastRow)
> For Each c In MyRange
> If IsDate(c) Then
> c.Offset(, -6).Value = DateAdd("d", 7, c.Value)
> End If
> Next
> End Sub
>
> --
> Mike
>
> When competing hypotheses are otherwise equal, adopt the hypothesis that
> introduces the fewest assumptions while still sufficiently answering the
> question.
>
> "tomjoe" wrote:
>
> > I want the code to pick dates from column Q (see line 4) add 7 days and put
> > the new dates into column k.
> >
> > Any suggestions?
> >
> > Sub copy_Dates()
> > Sheets(2).Range("e4:e65536").Value = Sheets(1).Range("d4:d65536")
> > Sheets(1).Range("n4:Q65536").Value = Sheets(2).Range("f4:i65536").Value
> > Sheets(1).Range("k4:K65536").Value = Range("q4:q65536").Value
> > Sheets(1).Select
> > Range("k65536").End(xlUp).Activate
> > If ActiveCell.Value <= Now - 30 Then
> > msgbox "NICE!!"
> > Else: Exit Sub
> > End If
> > End Sub
> >

--

Dave Peterson