morning,

i having some issues with the below code.
it is supposed to search for the date today minus 12 months, and then copy
all rows in between to a new sheet.
any suggestions would be greatly appreciated!
thanks very much on this very cold and foggy morning!!
mike

Private Sub CommandButton2_Click()
Dim Rng As range
Dim rCell As range
Dim copyRng As range
Dim destRng As range
Dim mydate As Date
Dim sh As Worksheet
Dim CalcMode As Long
Dim arr As Variant
Dim res As Variant
Dim mymonth As Date


mydate = Date
mymonth = Month(mydate)
Set sh = Sheets("Sheet 1")
Set Rng = sh.range("A5:A100")
Set destRng = Sheets("Sheet 2").range("A2")

res = mydate - mymonth

If res = "" Then Exit Sub
arr = Split(res, " ")

With application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With


For Each rCell In Rng.Cells
If Not IsError(application.Match(rCell.Value, arr, 0)) Then
If copyRng Is Nothing Then
Set copyRng = rCell
Else
Set copyRng = Union(rCell, copyRng)
End If
End If
Next rCell

If Not copyRng Is Nothing Then
copyRng.EntireRow.Copy Destination:=destRng
Else
'nothing found, do nothing
End If

With application
.Calculation = CalcMode
.ScreenUpdating = True
End With
End Sub

Hi Mike,

I believe that the code you are trying to use was originally suggested by me
for a very different purpose: to copy rows containing one of several words
supplied by a user in response to an input box.Certainly, as used, the code
is inappropriate for your current purpose.

In order to assist you, you will need to explain the layout of your data and
your purpose: is the data sorted sequentially; are all rows older than 1
year to be copied; is data to be appended to existing data on the target
sheet, or is the existing data to be overwritten?


---
Regards,
Norman



"mike" wrote in message
...
morning,

i having some issues with the below code.
it is supposed to search for the date today minus 12 months, and then copy
all rows in between to a new sheet.
any suggestions would be greatly appreciated!
thanks very much on this very cold and foggy morning!!
mike

Private Sub CommandButton2_Click()
Dim Rng As range
Dim rCell As range
Dim copyRng As range
Dim destRng As range
Dim mydate As Date
Dim sh As Worksheet
Dim CalcMode As Long
Dim arr As Variant
Dim res As Variant
Dim mymonth As Date


mydate = Date
mymonth = Month(mydate)
Set sh = Sheets("Sheet 1")
Set Rng = sh.range("A5:A100")
Set destRng = Sheets("Sheet 2").range("A2")

res = mydate - mymonth

If res = "" Then Exit Sub
arr = Split(res, " ")

With application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With


For Each rCell In Rng.Cells
If Not IsError(application.Match(rCell.Value, arr, 0)) Then
If copyRng Is Nothing Then
Set copyRng = rCell
Else
Set copyRng = Union(rCell, copyRng)
End If
End If
Next rCell

If Not copyRng Is Nothing Then
copyRng.EntireRow.Copy Destination:=destRng
Else
'nothing found, do nothing
End If

With application
.Calculation = CalcMode
.ScreenUpdating = True
End With
End Sub

Norman,

you are correct.
it is the formula you provided.

basically, i have a sheet.
it contains a number of columns.
my overall purpose of the macro is to.....

search in column A for all dates that are up to and including one year ago
from today's date.
then in column B there is an amount value.
i wish to then add the amounts in column from today back to a year ago today.
if it easier to copy them to a new sheet then this would suffice.
i hope you can help.
thanks

mike
i wish to add the amount value th

"Norman Jones" wrote:

Hi Mike,

I believe that the code you are trying to use was originally suggested by me
for a very different purpose: to copy rows containing one of several words
supplied by a user in response to an input box.Certainly, as used, the code
is inappropriate for your current purpose.

In order to assist you, you will need to explain the layout of your data and
your purpose: is the data sorted sequentially; are all rows older than 1
year to be copied; is data to be appended to existing data on the target
sheet, or is the existing data to be overwritten?


---
Regards,
Norman



"mike" wrote in message
...
morning,

i having some issues with the below code.
it is supposed to search for the date today minus 12 months, and then copy
all rows in between to a new sheet.
any suggestions would be greatly appreciated!
thanks very much on this very cold and foggy morning!!
mike

Private Sub CommandButton2_Click()
Dim Rng As range
Dim rCell As range
Dim copyRng As range
Dim destRng As range
Dim mydate As Date
Dim sh As Worksheet
Dim CalcMode As Long
Dim arr As Variant
Dim res As Variant
Dim mymonth As Date


mydate = Date
mymonth = Month(mydate)
Set sh = Sheets("Sheet 1")
Set Rng = sh.range("A5:A100")
Set destRng = Sheets("Sheet 2").range("A2")

res = mydate - mymonth

If res = "" Then Exit Sub
arr = Split(res, " ")

With application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With


For Each rCell In Rng.Cells
If Not IsError(application.Match(rCell.Value, arr, 0)) Then
If copyRng Is Nothing Then
Set copyRng = rCell
Else
Set copyRng = Union(rCell, copyRng)
End If
End If
Next rCell

If Not copyRng Is Nothing Then
copyRng.EntireRow.Copy Destination:=destRng
Else
'nothing found, do nothing
End If

With application
.Calculation = CalcMode
.ScreenUpdating = True
End With
End Sub

Hi Mike,

Are the rows sorted in date order?

Is there additional data in columns C == that should be copied?

If your purpose is to extract information for a one year period, why not use
the autofilter feature to extract all rows between your required dates. You
could then use the SubTotal function to sum the filtered rows, e.g.:

=SUBTOTAL(9,B:B)

---
Regards,
Norman



"mike" wrote in message
...
Norman,

you are correct.
it is the formula you provided.

basically, i have a sheet.
it contains a number of columns.
my overall purpose of the macro is to.....

search in column A for all dates that are up to and including one year ago
from today's date.
then in column B there is an amount value.
i wish to then add the amounts in column from today back to a year ago
today.
if it easier to copy them to a new sheet then this would suffice.
i hope you can help.
thanks

mike
i wish to add the amount value th

"Norman Jones" wrote:

Hi Mike,

I believe that the code you are trying to use was originally suggested by
me
for a very different purpose: to copy rows containing one of several
words
supplied by a user in response to an input box.Certainly, as used, the
code
is inappropriate for your current purpose.

In order to assist you, you will need to explain the layout of your data
and
your purpose: is the data sorted sequentially; are all rows older than 1
year to be copied; is data to be appended to existing data on the target
sheet, or is the existing data to be overwritten?


---
Regards,
Norman



"mike" wrote in message
...
morning,

i having some issues with the below code.
it is supposed to search for the date today minus 12 months, and then
copy
all rows in between to a new sheet.
any suggestions would be greatly appreciated!
thanks very much on this very cold and foggy morning!!
mike

Private Sub CommandButton2_Click()
Dim Rng As range
Dim rCell As range
Dim copyRng As range
Dim destRng As range
Dim mydate As Date
Dim sh As Worksheet
Dim CalcMode As Long
Dim arr As Variant
Dim res As Variant
Dim mymonth As Date


mydate = Date
mymonth = Month(mydate)
Set sh = Sheets("Sheet 1")
Set Rng = sh.range("A5:A100")
Set destRng = Sheets("Sheet 2").range("A2")

res = mydate - mymonth

If res = "" Then Exit Sub
arr = Split(res, " ")

With application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With


For Each rCell In Rng.Cells
If Not IsError(application.Match(rCell.Value, arr, 0)) Then
If copyRng Is Nothing Then
Set copyRng = rCell
Else
Set copyRng = Union(rCell, copyRng)
End If
End If
Next rCell

If Not copyRng Is Nothing Then
copyRng.EntireRow.Copy Destination:=destRng
Else
'nothing found, do nothing
End If

With application
.Calculation = CalcMode
.ScreenUpdating = True
End With
End Sub

Norman can Autofilter be used in macros?

There is data in other columns, but i need only 2 other columns at most.
the rows are sorted by date order but are not sequential, eg the dates will
run in month order, but days may be skipped or doubled up, where there were
no entries for some days or more on another day.
i am trying to format it in a macro, because i have wrote something similar,
using your help before, which looks for a certain word in column a then
copies all information relating to that word, ie all rows that contain the
word, and the relevant information in those rows, to a new sheet, which is
then automatically mailed to someone.

what i am trying to do now is collate the number values to complile reports
and email them automatically. this is just the start, my manager also asked
if it would be possible to analyse the amounts on a quarter on quarter basis
to see if there is a reduction in frequency of the values.

i hope you can help, i think im getting a bit out of my depth!! might have
to go and buy a textbook for this stuff at lunch!!!

thanks again

mike

"Norman Jones" wrote:

Hi Mike,

Are the rows sorted in date order?

Is there additional data in columns C == that should be copied?

If your purpose is to extract information for a one year period, why not use
the autofilter feature to extract all rows between your required dates. You
could then use the SubTotal function to sum the filtered rows, e.g.:

=SUBTOTAL(9,B:B)

---
Regards,
Norman



"mike" wrote in message
...
Norman,

you are correct.
it is the formula you provided.

basically, i have a sheet.
it contains a number of columns.
my overall purpose of the macro is to.....

search in column A for all dates that are up to and including one year ago
from today's date.
then in column B there is an amount value.
i wish to then add the amounts in column from today back to a year ago
today.
if it easier to copy them to a new sheet then this would suffice.
i hope you can help.
thanks

mike
i wish to add the amount value th

"Norman Jones" wrote:

Hi Mike,

I believe that the code you are trying to use was originally suggested by
me
for a very different purpose: to copy rows containing one of several
words
supplied by a user in response to an input box.Certainly, as used, the
code
is inappropriate for your current purpose.

In order to assist you, you will need to explain the layout of your data
and
your purpose: is the data sorted sequentially; are all rows older than 1
year to be copied; is data to be appended to existing data on the target
sheet, or is the existing data to be overwritten?


---
Regards,
Norman



"mike" wrote in message
...
morning,

i having some issues with the below code.
it is supposed to search for the date today minus 12 months, and then
copy
all rows in between to a new sheet.
any suggestions would be greatly appreciated!
thanks very much on this very cold and foggy morning!!
mike

Private Sub CommandButton2_Click()
Dim Rng As range
Dim rCell As range
Dim copyRng As range
Dim destRng As range
Dim mydate As Date
Dim sh As Worksheet
Dim CalcMode As Long
Dim arr As Variant
Dim res As Variant
Dim mymonth As Date


mydate = Date
mymonth = Month(mydate)
Set sh = Sheets("Sheet 1")
Set Rng = sh.range("A5:A100")
Set destRng = Sheets("Sheet 2").range("A2")

res = mydate - mymonth

If res = "" Then Exit Sub
arr = Split(res, " ")

With application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With


For Each rCell In Rng.Cells
If Not IsError(application.Match(rCell.Value, arr, 0)) Then
If copyRng Is Nothing Then
Set copyRng = rCell
Else
Set copyRng = Union(rCell, copyRng)
End If
End If
Next rCell

If Not copyRng Is Nothing Then
copyRng.EntireRow.Copy Destination:=destRng
Else
'nothing found, do nothing
End If

With application
.Calculation = CalcMode
.ScreenUpdating = True
End With
End Sub

Hi Mike,

Norman can Autofilter be used in macros?

Certainly!

Turn on the macro recorder, perform the required operations manually. This
will provide you with base code that can be edited to improve efficiency and
render it appropriate for general application.

If you require any assistance with such editing, post back with specifics.

From your description, I think that the autofilter will best meet your
requirements.

---
Regards,
Norman



"mike" wrote in message
...
Norman can Autofilter be used in macros?

There is data in other columns, but i need only 2 other columns at most.
the rows are sorted by date order but are not sequential, eg the dates
will
run in month order, but days may be skipped or doubled up, where there
were
no entries for some days or more on another day.
i am trying to format it in a macro, because i have wrote something
similar,
using your help before, which looks for a certain word in column a then
copies all information relating to that word, ie all rows that contain the
word, and the relevant information in those rows, to a new sheet, which is
then automatically mailed to someone.

what i am trying to do now is collate the number values to complile
reports
and email them automatically. this is just the start, my manager also
asked
if it would be possible to analyse the amounts on a quarter on quarter
basis
to see if there is a reduction in frequency of the values.

i hope you can help, i think im getting a bit out of my depth!! might have
to go and buy a textbook for this stuff at lunch!!!

thanks again

mike

"Norman Jones" wrote:

Hi Mike,

Are the rows sorted in date order?

Is there additional data in columns C == that should be copied?

If your purpose is to extract information for a one year period, why not
use
the autofilter feature to extract all rows between your required dates.
You
could then use the SubTotal function to sum the filtered rows, e.g.:

=SUBTOTAL(9,B:B)

---
Regards,
Norman



"mike" wrote in message
...
Norman,

you are correct.
it is the formula you provided.

basically, i have a sheet.
it contains a number of columns.
my overall purpose of the macro is to.....

search in column A for all dates that are up to and including one year
ago
from today's date.
then in column B there is an amount value.
i wish to then add the amounts in column from today back to a year ago
today.
if it easier to copy them to a new sheet then this would suffice.
i hope you can help.
thanks

mike
i wish to add the amount value th

"Norman Jones" wrote:

Hi Mike,

I believe that the code you are trying to use was originally suggested
by
me
for a very different purpose: to copy rows containing one of several
words
supplied by a user in response to an input box.Certainly, as used, the
code
is inappropriate for your current purpose.

In order to assist you, you will need to explain the layout of your
data
and
your purpose: is the data sorted sequentially; are all rows older than
1
year to be copied; is data to be appended to existing data on the
target
sheet, or is the existing data to be overwritten?


---
Regards,
Norman



"mike" wrote in message
...
morning,

i having some issues with the below code.
it is supposed to search for the date today minus 12 months, and
then
copy
all rows in between to a new sheet.
any suggestions would be greatly appreciated!
thanks very much on this very cold and foggy morning!!
mike

Private Sub CommandButton2_Click()
Dim Rng As range
Dim rCell As range
Dim copyRng As range
Dim destRng As range
Dim mydate As Date
Dim sh As Worksheet
Dim CalcMode As Long
Dim arr As Variant
Dim res As Variant
Dim mymonth As Date


mydate = Date
mymonth = Month(mydate)
Set sh = Sheets("Sheet 1")
Set Rng = sh.range("A5:A100")
Set destRng = Sheets("Sheet 2").range("A2")

res = mydate - mymonth

If res = "" Then Exit Sub
arr = Split(res, " ")

With application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With


For Each rCell In Rng.Cells
If Not IsError(application.Match(rCell.Value, arr, 0)) Then
If copyRng Is Nothing Then
Set copyRng = rCell
Else
Set copyRng = Union(rCell, copyRng)
End If
End If
Next rCell

If Not copyRng Is Nothing Then
copyRng.EntireRow.Copy Destination:=destRng
Else
'nothing found, do nothing
End If

With application
.Calculation = CalcMode
.ScreenUpdating = True
End With
End Sub