Thank you - will test out the code and see how it turns out. much
appreciated.

On Sep 2, 5:01*pm, J. Andrew Smith
wrote:
Rule #1: *Never* use Cut Copy and Paste methods in macros, because the user
might be multi-tasking, and you're messing with the *Window* clipboard.

OK, here's how to do all this without CCP, and faster too:

* * With Application
* * * * .Calculation = xlCalculationManual
* * * * .ScreenUpdating = False
* * End With

* * With Worksheets("Interest")

* * * * With .Range("DI28", .Range("E29").End(xlDown)) ' Define upper right
to lower left corners of whole range

* * * * * * .Rows(1).AutoFill .Cells, xlFillCopy ' copies formulas with
relative addressing
* * * * * * Application.Calculate ' recalculates formulas
* * * * * * .Cells.Value = .Cells.Value ' replaces formulas with values

* * * * End With

* * * * .Range("G5").Value = Now() ' Not the Excel function, the VBA function

* * End With

* * With Worksheets("Principal")
* * * * ' same code block
* * End With

--
J. Andrew Smith
Senior Systems Analyst
Standard & Poor''''''''s, NYC

"Active VBA" wrote:
Is there a way to simplify this code below?
If you could please suggest a revised code, it would be greatly
appreciated. Thanks.

Sub update_analysis()
'
' Macro recorded July 2008
'

'
* * Application.ScreenUpdating = False
* * Sheets("Interest").Select
* * Range("E28:DI28").Select
* * Selection.Copy
* * Range("E29").Select
* * Range(Selection, Selection.End(xlDown)).Select
* * Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
* * * * False, Transpose:=False
* * Application.CutCopyMode = False
* * Selection.Copy
* * Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
* * * * :=False, Transpose:=False
* * Range("G5").Select
* * ActiveCell.FormulaR1C1 = "=NOW()"
* * Selection.Copy
* * Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
* * * * :=False, Transpose:=False
* * Sheets("Principal").Select
* * Range("E28:DI28").Select
* * Selection.Copy
* * Range("E29").Select
* * Range(Selection, Selection.End(xlDown)).Select
* * Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
* * * * False, Transpose:=False
* * Application.CutCopyMode = False
* * Selection.Copy
* * Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
* * * * :=False, Transpose:=False
* * Range("G5").Select
* * ActiveCell.FormulaR1C1 = "=NOW()"
* * Selection.Copy
* * Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
* * * * :=False, Transpose:=False
* * * * * * Sheets("Leasing Analysis").Select
* * Range("A2").Select
* * Application.ScreenUpdating = True
End Sub

Thank you for your help in advance.

Best regards,

Active VBA

*** Sent via Developersdexhttp://www.developersdex.com***