A Couple of Macro Questions

  • Thread starter Thread starter Paul
  • Start date Start date
P

Paul

I would like a macro to automatically run if the date is
anywhere in a given 8 week period. For example, if the
date is greater then October 1, I would like the macro to
run and I only want it to run once.

The code I have put in place is:

If Date>=October1 then
Sheets("FORECAST").Select
Range("AA8:AA44").Select
Selection.Copy
Range("Z8:Z44").Select
Selection.PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1:A3").Select
Application.CutCopyMode = False
Else End

Can someone review and let me know if this is right. Also,
how do I only get it to run once?
 
Paul,

What "date"? Today's date or the one in a cell?
there is nothing in your code to add (or subtract) 8
weeks from either date.

Other then that, you just need place the code in the even
that will call it the one time you want it to run...
SelectionCahnge, WorkBookOpen, a command button....

hope that's helps?
 
Hi Sandie ...

Since I posted this, I refined my code. Here's what I have
now.

What I would like to have happen is that this macro to run
each time the spreadhseet is opened, but if it runs
successfully once, I don't want it to run again in that 8
week time period.

Any ideas?

Do you think my date range below will work?

Run Once
If Date >= october1 And Date <= december1 Then
Sheets("FORECAST").Select
Range("AA8:AA44").Select
Selection.Copy
Range("Z8:Z44").Select
Selection.PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1:A3").Select
Application.CutCopyMode = False
End If
 
If Range("AA7") = "Done" Then Exit Sub
If Date >= October1 Then
Sheets("FORECAST").Select
Range("AA8:AA44").Copy
Range("Z8:Z44").PasteSpecial Paste:=xlPasteValues
Range("A1:A3").Select
Range("AA7") = "Done"
Application.CutCopyMode = False
End If
 
Paul

You need to enter in the spreadsheet something when the
macro has been run. This will have to be deleted in a
Before_Print or Before_Save macro.

Just change the ranges and sheet names to this.
Sub MyCopy()
Dim done, MyDate As Date
Worksheets(2).Select
MyDate = Range("A1")
done = Range("A2").Value
'Exit if it has been done
If Not IsEmpty(done) Then
Exit Sub
End If
If MyDate >= #10/1/2003# And MyDate <= #12/31/2003#
Then
With Sheets("sheet1")
Range("g8:g44").Value = Range("A8:A44").Value
End With
End If
Range("A2").Value = "Y"
End Sub

sub Before_Print()
Range("A2").clearcontents
End Sub

Regards
Peter
 
Back
Top