Copy/Paste Macro issue

[yoitsmejy]

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

[Simon Lloyd]

I may be wrong but it looks to me that you are replacing all "1/1/1900" with "4/1/2011" the find, copy and pastes you do seem to be counter productive!

What is the error you are getting?

[yoitsmejy]

Well, the main goal is for data with 1/1/1900 to 3/31/2011 to be permanent with out any updates. The formula is in the 1/1/1900 cell. So i would like to have that formula to be copied and modified into the 4/1/2011 cell with the date on the formula to be changed to 4/1/2011.

[yoitsmejy]

The error i received is "Object variable or With block variable not set" and it is highlighting
Cells.Find(What:="4/1/2011", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _ MatchCase:=True, SearchFormat:=False).Activate

[yoitsmejy]

the weird thing is that when i tried the code with 3 worksheets, it works fine. But when i used it for 700+ worksheets, its giving me that error message....