can I hide actively operating XL windows?

[Martyn]

Hi,
I am working on a macro that does a huge number of copy/paste operations
between workbooks/worksheets. Without interrupting the process what are the
VBA macro commands for hiding the active XL windows and then turning the
active page visiblity on again?
TIA

 

[Mike W]

Hi,

Try the following

Application.ScreenUpdating = False

to stop the screen updating, and

Application.ScreenUpdating = True

to start updating again after the copy and paste operations are completed.

Regards

Mike

 

[Martyn]

Thanks Mike, I am using XL2000 and unfortunately that didn't help!.

 

[Dave Hawley]

Hi Martyn

How are you doing the Copy/Paste? to by-pass the copying to the
clipboard and paste direct to another range (same Workbook or another
open one) without activating it, use code like

Workbooks("Book1.xls").Sheets(1).Range("A1:A100").Copy _
Destination:=Workbooks("Book2.xls").Sheets(2).Range("A1")

***** Posted via: http://www.ozgrid.com
Excel Templates, Training & Add-ins.
Free Excel Forum http://www.ozgrid.com/forum *****

 

[NickHK]

Martyn,
What do mean "that didn't help" ?

NickHK

 

[Martyn]

NickHK,
I mean that opening of a workbook, copying a range of cells then opening
another workbook, pasting it there,...re-occuring of this process a lot of
times (approx 20-30) makes the monitor an unpleasent screen to watch. All I
needed is some macro command that will "hide" these screens just for the
duration of the copy/paste process from wrkbook to wrkbook....
Regards
Martyn

 

[Martyn]

Hi Dave,
I am using the below codes for opening-up the files and copy/pasting...
-------------------------------------------
strFileName = Application.GetOpenFilename("Excel Templates (*.xlt), *.xlt")
If strFileName = "" Then Exit Sub
Workbooks.Open(strFileName, Editable:=True).RunAutoMacros
Which:=xlAutoOpen

' After selecting the disered *.xlt file from above the copy-paste
process starts
' Application.ScreenUpdating = False <<< I removed this line couse it
didn't help.

Worksheets("Sheet13").Activate
Range("C21:C30").Select
Selection.Copy
Windows("ANALIZR4.XLS").Activate
Worksheets("Sheet1").Activate
Range("B1:B10").Select
ActiveSheet.Paste , True

-------------------------------------

The last 7 lines of the code above is beeing repeated for 10 times for
different cell ranges between same workbooks (from selected *.xlt to
ANALIZR4.XLS Sheet1). And the whole process is repeated right from the start
a second time for opening the second *.xlt file, doing similar copy
operations from there (and paste operations to ANALIZR4.XLS Sheet2).

I am using XL2000.
Can I do the copy/paste operations without activating it?. I'll appreciate
recommendations for simplifying my code and more importantly "hiding" the
windows during the copy/paste operation.

Regards
TIA
Martyn

 

[Martyn]

Thinking on your code, since I do not know the filename beforehand and use a
variable for reading the *.xlt file,
I should change your code to something like this?

Workbooks(strFileName) & ".xlt".Sheets(1).Range("A1:A100").Copy _
Destination:=Workbooks("Book2.xls").Sheets(2).Range("A1")

Am I correct?
Thanks

 

[Dave Hawley]

Untested but this should get you started

Dim wBookOpen As Workbook
strFileName = Application.GetOpenFilename _
("Excel Templates (*.xlt), *.xlt")
If strFileName = "" Then Exit Sub
Workbooks.Open(strFileName, Editable:=True).RunAutoMacros _
Which:=xlAutoOpen

Set wBookOpen = ActiveWorkbook
ThisWorkbook.Sheets("Sheet13").Range("C21:C30").Copy _
Destination:=wBookOpen.Sheets("Sheet1").Range("B1")

***** Posted via: http://www.ozgrid.com
Excel Templates, Training & Add-ins.
Free Excel Forum http://www.ozgrid.com/forum *****

 

[Martyn]

Received a "subscript out of range error" for the line:

ThisWorkbook.Sheets("Sheet13").Range("C21:C30").Copy _
Destination:=wBookOpen.Sheets("Sheet1").Range("B1")

In fact if I am not wrong, your code tries to copy the C21:C30 range of
Sheet13 from the selected *.xlt file onto the same file's Sheet1
Range(B1:B10)...
But my intention is to copy the C21:C30 range of Sheet13 from the selected
*.xlt file onto ANALIZR4.xls Sheet1 Range(B1:B10).
ANALIZR4.XLS is the file where the macro is and where the pasted data will
reside.
Regards
Martyn

 

[Martyn]

Dave, I've found the commands I need at last...

Application.Visible = False
' do the copy/paste here
Application.Visible = True

Thank you for helping me
Martyn

 

[NickHK]

Martyn,
I don't understand why you say "unpleasent screen to watch" as there should
be no visble changes.

NickHK

 

[Martyn]

NickHK,
Solved it with