Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
A Couple of Macro Questions
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
A Couple of Macro Questions
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? -----Original Message----- 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? . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
A Couple of Macro Questions
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 -----Original Message----- 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? -----Original Message----- 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? . . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
A Couple of Macro Questions
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 wrote in message ... 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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
A Couple of Macro Questions
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 -----Original Message----- 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? . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
couple of questions | Excel Discussion (Misc queries) | |||
A couple of questions | Excel Discussion (Misc queries) | |||
Couple of Questions | Excel Discussion (Misc queries) | |||
A couple of questions... | Excel Discussion (Misc queries) | |||
Couple more questions... | New Users to Excel |