yoitsmejy
04-15-2011, 12:46 PM
Hey guys,
I have a macro that should do the following:
Part 1
Find Date X
Copy Formula within X
Find Date Y
Paste Formula X
Part 2
Find Date X
Paste Special Values Only
Find Date X (the new cell with formula X)
Replace Date X on that cell to Date Y
This is what i got, but it is giving me an error message. Please help me. Thank you.
Sub Macro6()
Dim ws As Worksheet
For Each ws In Worksheets
ws.Activate
With ActiveSheet.Cells
Cells.Find(What:="1/1/1900", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=True, SearchFormat:=False).Activate
Selection.Copy
Cells.Find(What:="4/1/2011", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=True, SearchFormat:=False).Activate
ActiveSheet.Paste
Cells.Find(What:="1/1/1900", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=True, SearchFormat:=False).Activate
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Cells.Find(What:="1/1/1900", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Replace What:="1/1/1900", Replacement:="4/1/2011", LookAt:= _
xlPart, SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End With
Next ws
End Sub
I have a macro that should do the following:
Part 1
Find Date X
Copy Formula within X
Find Date Y
Paste Formula X
Part 2
Find Date X
Paste Special Values Only
Find Date X (the new cell with formula X)
Replace Date X on that cell to Date Y
This is what i got, but it is giving me an error message. Please help me. Thank you.
Sub Macro6()
Dim ws As Worksheet
For Each ws In Worksheets
ws.Activate
With ActiveSheet.Cells
Cells.Find(What:="1/1/1900", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=True, SearchFormat:=False).Activate
Selection.Copy
Cells.Find(What:="4/1/2011", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=True, SearchFormat:=False).Activate
ActiveSheet.Paste
Cells.Find(What:="1/1/1900", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=True, SearchFormat:=False).Activate
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Cells.Find(What:="1/1/1900", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Replace What:="1/1/1900", Replacement:="4/1/2011", LookAt:= _
xlPart, SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End With
Next ws
End Sub