can I hide actively operating XL windows?

  • Thread starter Thread starter Martyn
  • Start date Start date
M

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
 
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
 
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,
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
 
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
 
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
 
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 *****
 
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
 
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
 
Martyn,
I don't understand why you say "unpleasent screen to watch" as there should
be no visble changes.

NickHK
 
Back
Top