Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2
Default Application.ScreenUpdating = False

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


"Tim" wrote in message om...
| 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


  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 7
Default Application.ScreenUpdating = False

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" wrote in message ...
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


"Tim" wrote in message om...
| 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

  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2
Default Application.ScreenUpdating = False

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


"Tim" wrote in message m...
| 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" wrote in message ...
| 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
|
|
| "Tim" wrote in message om...
| | 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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

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
Why does Selection.PasteSpecial toggle Application.ScreenUpdating OssieMac Excel Worksheet Functions 2 August 4th 09 12:05 AM
How to set Application.ScreenUpdating = False for Gen use David_Williams_PG () Excel Discussion (Misc queries) 1 August 15th 06 12:06 PM
What does ScreenUpdating = False do? Judy Ward Excel Worksheet Functions 5 July 9th 05 09:25 AM
Application.ScreenUpdating function question Fleone Excel Worksheet Functions 2 May 12th 05 01:36 AM


All times are GMT +1. The time now is 12:53 PM.

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

About Us

"It's about Microsoft Excel"