= Today()+1 into Sheet1, Sheet2, and Sheet3

J

Jazz

I am trying to stick the formula “= Today()+1†into E3:F3 on Sheet1 which is
already merged, C1 on Sheet2, and A3 on Sheet3. However, the problem I am
having with the code below is that it sometimes sticks the formula
“=Today()+1 into the wrong cells or not into any of the cells at all. Can
you help me make this work?


Sub Todaysdate()
Sheets("Sheet1").Activate
ActiveCell.FormulaR1C1 = "=TODAY()+1"
Range("E3:F3").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Sheets("Sheet2").Activate
ActiveCell.FormulaR1C1 = "=TODAY()+1"
Range("C1").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Sheets("Sheet3").Activate
Range("A3").Select
ActiveCell.FormulaR1C1 = "=TODAY()+1"
Range("A3").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Sheets("Sheet1").Activate
End Sub
 
P

Per Jessen

Hi
Try this, just make sure the cells are formated as Date:

Sub Todaysdate()
With Sheets("Sheet1").Range("E3")
.FormulaR1C1 = "=TODAY()+1"
.Copy
.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks
_
:=False, Transpose:=False
End With
With Sheets("Sheet2").Range("C1")
.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks
_
:=False, Transpose:=False
End With
With Sheets("Sheet3").Range("A3")
.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks
_
:=False, Transpose:=False
End With
Application.CutCopyMode = False
Sheets("Sheet1").Activate
End Sub

Or maybe better:

Sub Todaysdate1()
Dim MyDate As Date

MyDate = Date + 1
Sheets("Sheet1").Range("E3") = MyDate
Sheets("Sheet2").Range("C1") = MyDate
Sheets("Sheet3").Range("A3") = MyDate

Sheets("Sheet1").Activate
End Sub

Regards,
Per
 
J

Jazz

Dude this is sweet. Thanks for your help man!

Per Jessen said:
Hi
Try this, just make sure the cells are formated as Date:

Sub Todaysdate()
With Sheets("Sheet1").Range("E3")
.FormulaR1C1 = "=TODAY()+1"
.Copy
.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks
_
:=False, Transpose:=False
End With
With Sheets("Sheet2").Range("C1")
.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks
_
:=False, Transpose:=False
End With
With Sheets("Sheet3").Range("A3")
.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks
_
:=False, Transpose:=False
End With
Application.CutCopyMode = False
Sheets("Sheet1").Activate
End Sub

Or maybe better:

Sub Todaysdate1()
Dim MyDate As Date

MyDate = Date + 1
Sheets("Sheet1").Range("E3") = MyDate
Sheets("Sheet2").Range("C1") = MyDate
Sheets("Sheet3").Range("A3") = MyDate

Sheets("Sheet1").Activate
End Sub

Regards,
Per
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top