Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Why does Selection.PasteSpecial toggle Application.ScreenUpdating | Excel Worksheet Functions | |||
How to set Application.ScreenUpdating = False for Gen use | Excel Discussion (Misc queries) | |||
What does ScreenUpdating = False do? | Excel Worksheet Functions | |||
Application.ScreenUpdating function question | Excel Worksheet Functions |