Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Improving the code from a recorded macro
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 a 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, |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Improving the code from a recorded macro
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) "Montse" wrote in message ... 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 a 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, |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Improving the code from a recorded macro
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 -- Gary "Montse" wrote in message ... 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 a 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Tidying Recorded Macro Code | Excel Programming | |||
Simplifying Excel Recorded Macro Code | Excel Programming | |||
Call recorded macro from VBA code | Excel Programming | |||
Editing recorded macro code. Referencing workbook name in functio | Excel Programming | |||
looking to simplify a recorded macro with code | Excel Programming |