screen updating = false

[cmefly]

hi,

i've got a macro that unhide and hides sheets.

i'm using:

Application.ScreenUpdating = False
Application.EnableEvents = False

at the beginning....but when i run the macro i can still see the sheets being unhidden....

any way to not see this?

Marc

 

[kpark91]

Why do you need the sheets to be unhidden in the first place?

 

[cmefly]

Because i use them to copy data from.......

it's a massive macro that took me all night to do....

please say there is a way around this?

 

[kpark91]

Because i use them to copy data from.......

it's a massive macro that took me all night to do....

please say there is a way around this?

You don't need to unhide a worksheet in order to copy data.

Paste your code here and let's see if we can optimize it and figure out why you must unhide the worksheet.

 

[Smitty]

You don't need to unhide sheets in order to copy from them.

How about posting some of your code?

 

[cmefly]

okay...so i have to macros...one that i use to call a bunch of macros...and then the actual macros...i'll post the main one and one other given that the rest are pretty similiar.

The macros listed below makes modifications to specific worksheets...

I applogize in advance but i can't use the code function that this forum has. (EDIT: Yes you can --> [ code ] your code [ / code] - no spaces - Moderator)

Sub bringitalltogether()
'this macro brings all macros for collection together
 
Application.ScreenUpdating = False
Application.EnableEvents = False
 
 
Sheets("HIDDEN FINAL DVC").Visible = True
Sheets("HIDDEN FINAL DEF_VAR").Visible = True
Sheets("HIDDEN FINAL RATE RIDERS").Visible = True
Sheets("HIDDEN NETWORKING").Visible = True
Sheets("HIDDEN CONNECTING").Visible = True
 
 
IMPORT_DVC
IMPORT_PROPOSED_RR
IMPORT_DEF_VAR
IMPORT_networking
IMPORT_connecting
 
copyalldatatomainsheet
 
 
Sheets("HIDDEN FINAL DVC").Visible = false
Sheets("HIDDEN FINAL DEF_VAR").Visible = false
Sheets("HIDDEN FINAL RATE RIDERS").Visible = false
Sheets("HIDDEN NETWORKING").Visible = false
Sheets("HIDDEN CONNECTING").Visible = false
 
Application.ScreenUpdating = True
Application.EnableEvents = True
 
End Sub
 
 
Sub IMPORT_DVC()
 
Application.ScreenUpdating = False
Application.EnableEvents = False
 
 
Sheets("HIDDEN FINAL DVC").Select 'i get the error when the codes hits here
Rows("2:20000").Select
Selection.ClearContents
Range("A2").Select
 
 
Sheets("HIDDEN FINAL DVC").Select
Range("A2").Select
ActiveCell.FormulaR1C1 = "=hidden1!R[-1]C"
 
Range("B2").Select
ActiveCell.FormulaR1C1 = "prices"
 
Range("C2").Select
ActiveCell.FormulaR1C1 = _
"=ROUND(VLOOKUP(hidden1!R[-1]C[-2], '17. GDP-IPI - X'!R[20]C[-2]:R[105]C[12], 14,0),4)"
 
Range("d2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(hidden1!R[-1]C[-3], '17. GDP-IPI - X'!R22C1:R107C15, 10,0)"
 
Range("A2:D2").Select
Selection.AutoFill Destination:=Range("A2:D23"), Type:=xlFillDefault
Range("A2:D23").Select
Range("A2").Select
 
Range("E2").Select
ActiveCell.FormulaR1C1 = "=IF(ISERROR(RC[-1]), ""DELETE"", """")"
Range("E2").Select
Selection.AutoFill Destination:=Range("E2:E23"), Type:=xlFillDefault
Range("E2:E23").Select
Range("E9").Select
 
For i = 23 To 2 Step -1
 
If Range("E" & i).Value = "DELETE" Then
Rows(i & ":" & i).Select
Selection.Delete
End If
 
Next i
 
Application.EnableEvents = True
Application.ScreenUpdating = True
 
 
End Sub

 

[kpark91]

Ok. That's probably the reason.

You must get rid of all the select commands.

For example,

Range("E2").Select
Selection.AutoFill Destination:=Range("E2:E23"), Type:=xlFillDefault

can be reduced to

Range("E2").AutoFill Destination:=Range("E2:E23"), Type:=xlFillDefault

Moreover, where are these macros? (which modules?)

2) you must make sure your worksheet name exists. What error are you getting from

Sheets("HIDDEN FINAL DVC").Select 'i get the error when the codes hits here

3) Use code tags for this forum. :P

 

[cmefly]

the error i get is:

run-time error '1004'

select method of worksheet class failed.

 

[kpark91]

yeah. just try cleaning up the code and then running it alone with the desired worksheets hidden.

If you are still having trouble, post the code you have cleaned up and then we can go on from there.

Select method is usually really really slow and unreliable at times and I'm guessing the reason why you need to unhide the worksheets lies in select methods.

 

[cmefly]

so what do i do if i have to jump between worksheets?