Improving the code from a recorded macro

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Montse - 16 Sep 2007 23:20 GMT

I have recorded a macro that makes a workbook visible, it selects some
information, copies it, pastes it as values, copies it again to have it ready
for pasting it in another worksheet, and then it hides the workbook. All
works perfectly with the only exception that it is not very neat because the
workbook appears and disappears for a fraction of second, like the flash of a
camera.

My two questions are:
1. Is there any alternative code to do it in a more neat way?
2. I would like that "Range(B14:V21").Select" selects only the rows with
information (e.g. if only the first three rows have information, then it
selects B14:V16 only).

I wonder who could help me with this?

The current code is:

Sub CopyPaste()

Sheets("CopyPaste").Visible = True
Sheets("CopyPaste").Select
Range("B5:V12").Select
Selection.Copy
Range("B14:V21").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.Copy
Sheets("PV").Select
Range("B2").Select
Sheets("CopyPaste").Visible = False

End Sub

Thank you,

Reply to this Message

Bob Phillips - 16 Sep 2007 23:49 GMT

Try adding

Application.ScreenUpdating = False

at the start, and reset at the end.

Signature

---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

>I have recorded a macro that makes a workbook visible, it selects some
> information, copies it, pastes it as values, copies it again to have it
[quoted text clipped - 35 lines]
>
> Thank you,

Reply to this Message

Gary Keramidas - 17 Sep 2007 00:23 GMT

this may help what you have, bit there isn't enough information to answer your
other questions. plus, what happens after the 2nd copy statement?

Sub CopyPaste()
Dim ws As Worksheet
Set ws = Sheets("CopyPaste")
Application.ScreenUpdating = False

With ws
.Visible = True
With .Range("B5:V12")
.Copy
.PasteSpecial Paste:=xlPasteValues
.Copy
End With
Sheets("PV").Range("B2").Select
.Visible = False
End With
Application.ScreenUpdating = True
End Sub

Signature

Gary

>I have recorded a macro that makes a workbook visible, it selects some
> information, copies it, pastes it as values, copies it again to have it ready
[quoted text clipped - 32 lines]
>
> Thank you,

Reply to this Message