Improving the code from a recorded macro

[Montse]

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,

 

[Bob Phillips]

Try adding

Application.ScreenUpdating = False

at the start, and reset at the end.

--
---
HTH

Bob

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

 

[Gary Keramidas]

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