Re: Application.ScreenUpdating = False

[Pieter Kuyck]

Tim

You use Activate and Select so the Windows will show despite .ScreenUpdating = False.

Combine the functions of copy - paste to one line
'Copy the YEAR - in one line with space between ....Copy Workbooks.....
Workbooks(OldFileName).Sheets("Company").Range("K3:N6").Copy Workbooks(NewFileName).Sheets("Company").Range("K3:N6")

it's also faster

Pieter


| I have a macro that opens another excel file and copes info from it
| into the file that is running the macro. The info is copies from 20
| different sheets and is copied into 20 different sheets. I use
| Application.ScreenUpdating = False but the screen still flashes and is
| updated every time I go from workbook to workbook. What can I do to
| the code below that will update the screen only after all info has
| been copies?
|
|
|
|
| Application.ScreenUpdating = False
| Application.DisplayAlerts = False
|
| 'Copy the YEAR
| Windows(OldFileName).Activate
| Sheets("Company").Select
| Range("K3:N6").Copy
|
| Windows(NewFileName).Activate
| Sheets("Company").Select
| Range("K3:N6").Select
| ActiveSheet.Paste
|
| 'Copy the Company Info
| Windows(OldFileName).Activate
| Sheets("Company").Select
| Range("F9:I16").Copy
|
| Windows(NewFileName).Activate
| Sheets("Company").Select
| Range("F9:I16").Select
| ActiveSheet.Paste
|
|
| Application.CutCopyMode = False
|
| 'Close Old File
| Windows(OldFileName).Activate
| ActiveWindow.Close

 

[Tim]

Thanks for the help. I'm still new to VBA. I will use the line of
code you provided but how would I seperate it into seperate lines? I
guess what I am curious about is how to switch between workbooks and
sheets without using Activate and / or Select.

 

[Pieter Kuyck]

Tim

The code i provided is in one line and it copies the range(K3:N6) from workbook Oldfilename to the workbook(newfilename)
in the same range.
When i want to seperate it in several lines you must use the underscore
This is the same as in one line (space _ for seperation);
Workbooks(OldFileName). _
Sheets("Company"). _
Range("K3:N6"). _
Copy Workbooks(NewFileName). _
Sheets("Company"). _
Range("K3:N6")

In VBA most times you don't need to Select or Activate, that's only for the interaction with the user.
A Workbook has Sheets and
a (work)sheet has a Range and a
range has the methode Copy, which will copy the Range
With the Copy you can use a parameter, another range
Than it will copy to that range

Read more in the Help - Range and Sheets

Pieter


| Thanks for the help. I'm still new to VBA. I will use the line of
| code you provided but how would I seperate it into seperate lines? I
| guess what I am curious about is how to switch between workbooks and
| sheets without using Activate and / or Select.
|
|
| > Tim
| >
| > You use Activate and Select so the Windows will show despite .ScreenUpdating = False.
| >
| > Combine the functions of copy - paste to one line
| > 'Copy the YEAR - in one line with space between ....Copy Workbooks.....
| > Workbooks(OldFileName).Sheets("Company").Range("K3:N6").Copy Workbooks(NewFileName).Sheets("Company").Range("K3:N6")
| >
| > it's also faster
| >
| > Pieter
| >
| >
| > | I have a macro that opens another excel file and copes info from it
| > | into the file that is running the macro. The info is copies from 20
| > | different sheets and is copied into 20 different sheets. I use
| > | Application.ScreenUpdating = False but the screen still flashes and is
| > | updated every time I go from workbook to workbook. What can I do to
| > | the code below that will update the screen only after all info has
| > | been copies?
| > |
| > |
| > |
| > |
| > | Application.ScreenUpdating = False
| > | Application.DisplayAlerts = False
| > |
| > | 'Copy the YEAR
| > | Windows(OldFileName).Activate
| > | Sheets("Company").Select
| > | Range("K3:N6").Copy
| > |
| > | Windows(NewFileName).Activate
| > | Sheets("Company").Select
| > | Range("K3:N6").Select
| > | ActiveSheet.Paste
| > |
| > | 'Copy the Company Info
| > | Windows(OldFileName).Activate
| > | Sheets("Company").Select
| > | Range("F9:I16").Copy
| > |
| > | Windows(NewFileName).Activate
| > | Sheets("Company").Select
| > | Range("F9:I16").Select
| > | ActiveSheet.Paste
| > |
| > |
| > | Application.CutCopyMode = False
| > |
| > | 'Close Old File
| > | Windows(OldFileName).Activate
| > | ActiveWindow.Close