Help with code for Macro!
 

Hello,
I'm trying to create a Macro that does the following:
1. Copies the values of 12 cells from Sheet1 to Sheet2.
2. Perform an import of data from a database that will over-write the values
in the 12 copied cells.
3. Paste the copied values back to their original cells on Sheet1 in 11
columns. The only column that cannot be copied back is the column for the
current month.

I've got #1-#2 completed, but am having problems with #3 because of one
catch. The "monthly" periods run from the 21st of the previous month to the
20th of the current month. So for example, when we get to August 21st, the
current system date should tell the Macro to NOT copy over the September
column, but DO copy over the August column.

In the Macro VB editor I've tried using many functions to do this but the
data comparison's all seem to work incorrectly. For instance, when I use
the [Date] function to return the current system date (8/3/2006) and compare
it like the following, it evaluates True!

If Date "8/29/2006" Then

So it thinks 8/3/2006 is greater than 8/29/2006 because it is looking at the
value, not the dates! I've tried to format it as smalldate and such but it
keeps failing. Any ideas/tips/tricks you could offer would be most
appreciated. Thanks!

Help with code for Macro!
 

Jason

try:

If Date DateSerial(2006, 8, 29) Then

Regards

Trevor


"Jason" wrote in message
...
Hello,
I'm trying to create a Macro that does the following:
1. Copies the values of 12 cells from Sheet1 to Sheet2.
2. Perform an import of data from a database that will over-write the
values in the 12 copied cells.
3. Paste the copied values back to their original cells on Sheet1 in 11
columns. The only column that cannot be copied back is the column for the
current month.

I've got #1-#2 completed, but am having problems with #3 because of one
catch. The "monthly" periods run from the 21st of the previous month to
the 20th of the current month. So for example, when we get to August
21st, the current system date should tell the Macro to NOT copy over the
September column, but DO copy over the August column.

In the Macro VB editor I've tried using many functions to do this but the
data comparison's all seem to work incorrectly. For instance, when I use
the [Date] function to return the current system date (8/3/2006) and
compare it like the following, it evaluates True!

If Date "8/29/2006" Then

So it thinks 8/3/2006 is greater than 8/29/2006 because it is looking at
the value, not the dates! I've tried to format it as smalldate and such
but it keeps failing. Any ideas/tips/tricks you could offer would be most
appreciated. Thanks!

Help with code for Macro!
 

Thanks! That works great...

Jason
"Trevor Shuttleworth" wrote in message
...
Jason

try:

If Date DateSerial(2006, 8, 29) Then

Regards

Trevor


"Jason" wrote in message
...
Hello,
I'm trying to create a Macro that does the following:
1. Copies the values of 12 cells from Sheet1 to Sheet2.
2. Perform an import of data from a database that will over-write the
values in the 12 copied cells.
3. Paste the copied values back to their original cells on Sheet1 in 11
columns. The only column that cannot be copied back is the column for
the current month.

I've got #1-#2 completed, but am having problems with #3 because of one
catch. The "monthly" periods run from the 21st of the previous month to
the 20th of the current month. So for example, when we get to August
21st, the current system date should tell the Macro to NOT copy over the
September column, but DO copy over the August column.

In the Macro VB editor I've tried using many functions to do this but the
data comparison's all seem to work incorrectly. For instance, when I use
the [Date] function to return the current system date (8/3/2006) and
compare it like the following, it evaluates True!

If Date "8/29/2006" Then

So it thinks 8/3/2006 is greater than 8/29/2006 because it is looking at
the value, not the dates! I've tried to format it as smalldate and such
but it keeps failing. Any ideas/tips/tricks you could offer would be
most appreciated. Thanks!

Help with code for Macro!
 

You're welcome. Thanks for the feedback


"Jason" wrote in message
...
Thanks! That works great...

Jason
"Trevor Shuttleworth" wrote in message
...
Jason

try:

If Date DateSerial(2006, 8, 29) Then

Regards

Trevor


"Jason" wrote in message
...
Hello,
I'm trying to create a Macro that does the following:
1. Copies the values of 12 cells from Sheet1 to Sheet2.
2. Perform an import of data from a database that will over-write the
values in the 12 copied cells.
3. Paste the copied values back to their original cells on Sheet1 in 11
columns. The only column that cannot be copied back is the column for
the current month.

I've got #1-#2 completed, but am having problems with #3 because of one
catch. The "monthly" periods run from the 21st of the previous month to
the 20th of the current month. So for example, when we get to August
21st, the current system date should tell the Macro to NOT copy over the
September column, but DO copy over the August column.

In the Macro VB editor I've tried using many functions to do this but
the data comparison's all seem to work incorrectly. For instance, when
I use the [Date] function to return the current system date (8/3/2006)
and compare it like the following, it evaluates True!

If Date "8/29/2006" Then

So it thinks 8/3/2006 is greater than 8/29/2006 because it is looking at
the value, not the dates! I've tried to format it as smalldate and such
but it keeps failing. Any ideas/tips/tricks you could offer would be
most appreciated. Thanks!