Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Simplify Code for Copy/Paste Special
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default Simplify Code for Copy/Paste Special
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 Developersdex http://www.developersdex.com ***
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,533
Default Simplify Code for Copy/Paste Special
Hi

It could look like this:

Sub update_analysis()
'
' Macro recorded July 2008
'

'
Application.ScreenUpdating = False
Sheets("Interest").Select
Range("E28:DI28").Copy
Range("E29", Range("E29").End(xlDown)).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").Value = Date & " " & Format(Time, "hh:mm")

Sheets("Principal").Select
Range("E28:DI28").Copy
Range("E29", Range("E29").End(xlDown)).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").Value = Date & " " & Format(Time, "hh:mm")

Sheets("Leasing Analysis").Select
Range("A2").Select
Application.ScreenUpdating = True
End Sub

Regards,
Per

"Active VBA" skrev i meddelelsen
...
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 Developersdex http://www.developersdex.com ***

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 10,124
Default Simplify Code for Copy/Paste Special
Something like this?

Sub update_analysis()
Application.ScreenUpdating = False
with Sheets("Interest")
.Range("E28:DI28").Copy
.Range("E29").End(xlDown)).PasteSpecial Paste:=xlPasteValues
.Range("G5")=date
end with

with Sheets("Principal")
.Range("E28:DI28").Copy
.Range("E29").End(xlDown)).PasteSpecial Paste:=xlPasteValues
.range("g5")=date
end with

'shouldn't be necessary
'Sheets("Leasing Analysis").Select
'Range("A2").Select

Application.ScreenUpdating = True
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Active VBA" wrote in message
...
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 Developersdex http://www.developersdex.com ***

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 10,124
Default Simplify Code for Copy/Paste Special
.Range("E29").End(xlDown)).PasteSpecial Paste:=xlPasteValues
Actually should be
..Range(Range("e29"), Range("E29").End(xlDown)).PasteSpecial
Paste:=xlPasteValues

Try this further simplification and correction
Sub sheetsinarray()
myarray = Array("Interest", "Principal")
For Each Sh In myarray
With Sheets(Sh)
.Range("E28:DI28").Copy
.Range(Range("e29"), Range("E29").End(xlDown)) _
.PasteSpecial Paste:=xlPasteValues
.Range("G5") = Now 'Date
End With
Next Sh
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...
Something like this?

Sub update_analysis()
Application.ScreenUpdating = False
with Sheets("Interest")
.Range("E28:DI28").Copy
.Range("E29").End(xlDown)).PasteSpecial Paste:=xlPasteValues
.Range("G5")=date
end with

with Sheets("Principal")
.Range("E28:DI28").Copy
.Range("E29").End(xlDown)).PasteSpecial Paste:=xlPasteValues
.range("g5")=date
end with

'shouldn't be necessary
'Sheets("Leasing Analysis").Select
'Range("A2").Select

Application.ScreenUpdating = True
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Active VBA" wrote in message
...
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 Developersdex http://www.developersdex.com ***


Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 18
Default Simplify Code for Copy/Paste Special
On Aug 31, 4:39*pm, "Don Guillett" wrote:
*.Range("E29").End(xlDown)).PasteSpecial Paste:=xlPasteValues
Actually should be
.Range(Range("e29"), Range("E29").End(xlDown)).PasteSpecial
Paste:=xlPasteValues

Try this further simplification and correction
Sub sheetsinarray()
myarray = Array("Interest", "Principal")
For Each Sh In myarray
With Sheets(Sh)
* * .Range("E28:DI28").Copy
* * .Range(Range("e29"), Range("E29").End(xlDown)) _
* * * * *.PasteSpecial Paste:=xlPasteValues
* * .Range("G5") = Now 'Date
End With
Next Sh
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"Don Guillett" wrote in message

...



Something like this?

Sub update_analysis()
* *Application.ScreenUpdating = False
with Sheets("Interest")
* *.Range("E28:DI28").Copy
* *.Range("E29").End(xlDown)).PasteSpecial Paste:=xlPasteValues
* *.Range("G5")=date
end with

with Sheets("Principal")
* *.Range("E28:DI28").Copy
* *.Range("E29").End(xlDown)).PasteSpecial Paste:=xlPasteValues
* *.range("g5")=date
end with

'shouldn't be necessary
'Sheets("Leasing Analysis").Select
'Range("A2").Select

Application.ScreenUpdating = True
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Active VBA" wrote in message
. ..
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***- Hide quoted text -

- Show quoted text -

Code you suggest did not work for some reason.?? Any suggestions.
Thanks, V.
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 18
Default Simplify Code for Copy/Paste Special
On Aug 31, 9:26*pm, wrote:
On Aug 31, 4:39*pm, "Don Guillett" wrote:





*.Range("E29").End(xlDown)).PasteSpecial Paste:=xlPasteValues
Actually should be
.Range(Range("e29"), Range("E29").End(xlDown)).PasteSpecial
Paste:=xlPasteValues

Try this further simplification and correction
Sub sheetsinarray()
myarray = Array("Interest", "Principal")
For Each Sh In myarray
With Sheets(Sh)
* * .Range("E28:DI28").Copy
* * .Range(Range("e29"), Range("E29").End(xlDown)) _
* * * * *.PasteSpecial Paste:=xlPasteValues
* * .Range("G5") = Now 'Date
End With
Next Sh
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"Don Guillett" wrote in message

...

Something like this?

Sub update_analysis()
* *Application.ScreenUpdating = False
with Sheets("Interest")
* *.Range("E28:DI28").Copy
* *.Range("E29").End(xlDown)).PasteSpecial Paste:=xlPasteValues
* *.Range("G5")=date
end with

with Sheets("Principal")
* *.Range("E28:DI28").Copy
* *.Range("E29").End(xlDown)).PasteSpecial Paste:=xlPasteValues
* *.range("g5")=date
end with

'shouldn't be necessary
'Sheets("Leasing Analysis").Select
'Range("A2").Select

Application.ScreenUpdating = True
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Active VBA" wrote in message
. ..
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***-Hide quoted text -

- Show quoted text -

Code you suggest did not work for some reason.?? Any suggestions.
Thanks, V.- Hide quoted text -

- Show quoted text -

any help on this will be greatly appreciated..............will keep my
spreadsheet to a manageable size (under 4 Meg as opposed to 34 Meg)
Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 10,124
Default Simplify Code for Copy/Paste Special
If desired, send your workbook to my address below along with snippets of
these emails and a clear explanation of what you want to happen along with
before/after examples.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

wrote in message
...
On Aug 31, 9:26 pm, wrote:
On Aug 31, 4:39 pm, "Don Guillett" wrote:





.Range("E29").End(xlDown)).PasteSpecial Paste:=xlPasteValues
Actually should be
.Range(Range("e29"), Range("E29").End(xlDown)).PasteSpecial
Paste:=xlPasteValues

Try this further simplification and correction
Sub sheetsinarray()
myarray = Array("Interest", "Principal")
For Each Sh In myarray
With Sheets(Sh)
.Range("E28:DI28").Copy
.Range(Range("e29"), Range("E29").End(xlDown)) _
.PasteSpecial Paste:=xlPasteValues
.Range("G5") = Now 'Date
End With
Next Sh
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"Don Guillett" wrote
in message

...

Something like this?

Sub update_analysis()
Application.ScreenUpdating = False
with Sheets("Interest")
.Range("E28:DI28").Copy
.Range("E29").End(xlDown)).PasteSpecial Paste:=xlPasteValues
.Range("G5")=date
end with

with Sheets("Principal")
.Range("E28:DI28").Copy
.Range("E29").End(xlDown)).PasteSpecial Paste:=xlPasteValues
.range("g5")=date
end with

'shouldn't be necessary
'Sheets("Leasing Analysis").Select
'Range("A2").Select

Application.ScreenUpdating = True
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Active VBA" wrote in message
. ..
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***-Hide quoted
text -

- Show quoted text -

Code you suggest did not work for some reason.?? Any suggestions.
Thanks, V.- Hide quoted text -

- Show quoted text -

any help on this will be greatly appreciated..............will keep my
spreadsheet to a manageable size (under 4 Meg as opposed to 34 Meg)

Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5
Default Simplify Code for Copy/Paste Special
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 Developersdex http://www.developersdex.com ***

Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 18
Default Simplify Code for Copy/Paste Special
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***

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Can't Copy and Paste or Paste Special between Excel Workbooks wllee Excel Discussion (Misc queries) 5 April 29th 23 03:43 AM
Automating copy/paste/paste special when row references change Carl LaFong Excel Programming 4 October 8th 07 06:10 AM
help w/ generic copy & paste/paste special routine DavidH[_2_] Excel Programming 5 January 23rd 06 03:58 AM
Dynamic Copy/Paste Special Formulas/Paste Special Values Sharon Perez Excel Programming 3 August 7th 04 09:49 PM
Copy Paste Special Value using Code over Several Worksheets John[_81_] Excel Programming 1 April 19th 04 12:09 PM


All times are GMT +1. The time now is 08:45 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement