hide spreadsheets

Bonjour m'sieu Amroo
I think you are looking for the 'ScreenUpdating Property' function. I found it in help while I was in the VBA editor. I am not much of a VBA user but the explanation (and examples) are fairly clear.
au revoir et bon chance
Ian

Hi Amroo

As Ian has suggested you could use the ScreenUpdating method to prevent the screen repainting itself. This will also speed up your code. To use it you would do this.

Application.ScreenUpdating=False
'Your Code
Application.ScreenUpdating=True

Although this is a handy method, there should be very few instances that would require the Selecting of cells and/or Worksheets, which is the biggest cause of screen flickering and slow running of code. If you could post your code here, I'd be happy to have a go at modifying it for you.

The code to hide a Worsheet would be.

Sheet1.Visible = xlSheetHidden

OR

Sheet1.Visible = xlSheetVeryHidden

...and finishing with

Sheet1.Visible = xlSheetVisible

Dave

OzGrid Business Applications

Merci to helpers, for DAve my code is :
the explications are in french if you want I try a translation, now by using english in this forum my level grow up.
I have a workbook (CRO2.xls) with 4 sheets
"data" where ID=CR
"import" for the data imported from a file.text
"prog" with specific table for the cellule to be filled
"ClinA-M" is the model table of prog
and an other workbook(ClinMar.xls) where the result of prog table filled were copied.
Amroo
'""""""""""""""""""""""""""""""""""""""""""""""
Sub MClinAM()
' MClinAM Macro
' Touche de raccourci du clavier: Ctrl+c
'"""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
'declaration des variables utilisées
Dim msgerreur
Dim mesgerror
Dim CR As Integer
Dim ws As Worksheet
Dim NM As Integer
Dim F As String

'""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
'vider le feuille import au début du prog
Windows("CR022.xls").Activate
Sheets("import").Activate
Cells.Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
'nettoyage de la feuille prog
Sheets("prog").Select
Cells.Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
'invite à choisir son fichier texte à importer
UserForm3.Show
Sheets("import").Activate
F = Range("I5").value
'ouverture du fichier du CRIH et mis en forme
'importation des données clinique du mois de janvier 2001
Workbooks.OpenText filename:=(F), Origin _
:=xlWindows, StartRow:=2, DataType:=xlFixedWidth, FieldInfo:=Array(Array _
(0, 1), Array(4, 1), Array(8, 1), Array(9, 1), Array(17, 1), Array(55, 2), Array(78, 2), _
Array(98, 2))
'élimination des colonnes inutiles
Columns("B:B").Select
Selection.Delete Shift:=xlToLeft
Columns("C:C").Select
Selection.Delete Shift:=xlToLeft
'insertion d'une colonne de concatenation et suppression des colonnes méres.
'recopie jusqu'a fin de CR
Columns("A:A").Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlToRight
Range("A1").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[1],RC[3])"
Selection.AutoFill Destination:=Range("A1:A866")
Range("A1:A866").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Columns("B:B").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("C:C").Select
Selection.Delete Shift:=xlToLeft
'tri par ordre alphabétique de la colonne B
Cells.Select
Selection.Sort Key1:=Range("B1"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Columns("B:B").Select
Selection.Delete Shift:=xlToLeft
'remplacer le point de mil par "" et formatage des données texte en numérique
Columns("B:D").Select
Selection.Replace What:=".", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Range("E1").Select
ActiveCell.FormulaR1C1 = "=VALUE(RC[-3])"
Selection.AutoFill Destination:=Range("E1:G1"), Type:=xlFillDefault
Range("E1:G1").Select
Selection.AutoFill Destination:=Range("E1:G807")
Range("E1:G807").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Columns("B:D").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Cells.Select
'tri par ordre croissant sur le CR
'Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Columns("A:D").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Selection.Copy
'recopy vers la feuille import du classeur essaiCR022.xls
Windows("CR022.xls").Activate
Sheets("import").Activate
Range("A1").Select
ActiveSheet.Paste
Range("I5").ClearContents
'ouverture du classeur Tableaux de bord ClinMois.xls
Workbooks.Open filename:="C:\Mes documents\Stock\ClinMars.xls"
'Application.WindowState = xlMinimized
Windows("CR022.xls").Activate
'Application.WindowState = xlMaximized
'recopy du modéle de tableau
Sheets("ClinA-M").Select
Cells.Select
Selection.Copy
Sheets("prog").Select
Range("A1").Select
ActiveSheet.Paste
'¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤
'introduction du chiffre du mois courant pour calcul % budget autorisé
NM = InputBox("entrer le chiffre du mois ex: fev:2, Juin:6")
Range("I78").value = NM
'début de procédure pour un traitement en boucle
Sheets("data").Select
Range("A38").Select
Do While ActiveCell.value <> ""
Selection.Offset(1, 0).Select
If ActiveCell = "" Then GoTo msg
ActiveCell.EntireRow.Select
Selection.Copy
Sheets("prog").Select
Range("A9").Select
ActiveSheet.Paste
' inscription du CR, Service,Nom du chef de service, UF et CS
Range("B5").Select
ActiveCell.FormulaR1C1 = "=R[4]C[-1]"
Range("C5").Select
ActiveCell.FormulaR1C1 = "=R[4]C"
Range("B6").Select
ActiveCell.FormulaR1C1 = "=R[3]C[2]"
Range("B7").Select
ActiveCell.FormulaR1C1 = "=R[2]C[3]"
Range("B8").Select
ActiveCell.FormulaR1C1 = "=R[1]C"
Range("C8").Select
ActiveCell.FormulaR1C1 = "=R[1]C"
Range("B5:C8").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Rows("9:9").Select
Application.CutCopyMode = False
Selection.ClearContents
'remplacement du CR par le suivant
CR = Range("B5").value
Cells.Replace What:="1111", Replacement:=CR, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
'¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤
'recopy vers la feuille moyens
Range("B5:C8").Select
Selection.Copy
Range("B68").Select
ActiveSheet.Paste
'Séries de recherche de valeurs et inscription dans le T.B
'Entrées,venues nb places théoriques, journées, % occupation,consult externes.
ActiveSheet.Paste
Range("B15:C15").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1,import!C1:C4,2,FALSE)"
Selection.AutoFill Destination:=Range("B15:C20"), Type:=xlFillDefault
Range("D15:E15").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1,import!C1:C4,3,FALSE)"
Selection.AutoFill Destination:=Range("D15:E20"), Type:=xlFillDefault
Range("D15:E20").Select
Range("B17:E17").Select
Selection.NumberFormat = "#,##0.00"
Range("B19:E19").Select
Selection.NumberFormat = "#,##0.00"
Range("B18:D18").Select
Application.CutCopyMode = False
Selection.Copy
Range("F15:G15").Select
ActiveCell.FormulaR1C1 = "=(RC[-2]-RC[-4])*100/RC[-4]"
Selection.AutoFill Destination:=Range("F15:G20"), Type:=xlFillDefault
'Dépenses médicales
Range("C78").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC1,import!C1:C4,IF(R75C=""budget"",4,IF(R75C=""consommations"",3)),FALSE)"
Range("C78").Select
Selection.AutoFill Destination:=Range("C78:D78"), Type:=xlFillDefault
Range("C78:D78").Select
'services techniques
Range("C80").Select
ActiveCell.FormulaR1C1 = "=R[1]C-(R[-1]C+R[-2]C)"
Range("C80").Select
Selection.AutoFill Destination:=Range("C80:D80"), Type:=xlFillDefault
Range("C80:D80").Select
'calcul % évolution fin mois 2001
Range("H76").Select
ActiveCell.FormulaR1C1 = "cmap"
Range("H78").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-7],import!C[-7]:C[-4],2,FALSE)"
Selection.AutoFill Destination:=Range("H78:H81"), Type:=xlFillDefault
Range("H78").Select
Range("H78:H81").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Selection.Replace What:=".", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Range("H80").Select
ActiveCell.FormulaR1C1 = "=R[1]C-(R[-1]C+R[-2]C)"
'% réel = consommation / budget
Range("F78").Select
ActiveCell.FormulaR1C1 = "=(RC[-2]/RC[-3])*100"
Selection.AutoFill Destination:=Range("F78:F81"), Type:=xlFillDefault
Range("F78:F81").Select
' % évolution consommation / (valeur n - 1)
Range("G78").Select
ActiveCell.FormulaR1C1 = "=(RC[-3]-RC[1])*100/RC[1]"
Selection.AutoFill Destination:=Range("G78:G81"), Type:=xlFillDefault
Range("G78:G81").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
'le % budget autorisé = num (mois en cours) /12
Range("E78").Select
ActiveCell.FormulaR1C1 = "=R78C[4]/12*100"
Selection.AutoFill Destination:=Range("E78:E81"), Type:=xlFillDefault
Range("E78:E81").Select
Selection.NumberFormat = "0.00"
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("I78").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
'suppression de données intermédiaires
Range("H75:H81").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
'Effectifs médicaux
Range("C104").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],import!C1:C4,2,FALSE)"
Range("C104").Select
Selection.AutoFill Destination:=Range("C104:C110"), Type:=xlFillDefault
Range("C104:C110").Select
Range("F104").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1,import!C1:C4,3,FALSE)"
Selection.AutoFill Destination:=Range("F104:F110"), Type:=xlFillDefault
Range("F104:F110").Select
'effectifs fonction publique
Range("C129").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1,import!C1:C4,2,FALSE)"
Range("C129").Select
Selection.AutoFill Destination:=Range("C129:C130"), Type:=xlFillDefault
Range("E125:F125").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1,import!C1:C4,3,FALSE)"
Range("E129:F129").Select
Selection.AutoFill Destination:=Range("E129:F130"), Type:=xlFillDefault
' dotations
Range("D114").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],import!C[-3]:C[-1],3,FALSE)"
' consolidation des données du tableau et suppression du CR
Range("B12:G130").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
' remplacement de la valeur #N/A par ""
Range("B12:G130").Select
Selection.Replace What:="#N/A", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True
'supression du CR et des intitulés
Range("A11:A130").Select
Selection.Replace What:=CR & "SAMOEN", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True
Range("B11:G130").Select
Cells.Select
Selection.Replace What:="#VALEUR!", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Range("A78").Select
Cells.Replace What:=CR & "DEPMED", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True
Range("A104").Select
Cells.Replace What:=CR & "EFFMED", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True
Range("B114").Select
Cells.Replace What:=CR & "DOTANN", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True
Range("A129").Select
Cells.Replace What:=CR & "MENSUA", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True
'ouverture du classeur du mois en cours
'Ajout d'une feuille si elle n'existe pas déja
'insertion en fin de classeur
Windows("ClinMars.xls").Activate
Application.WindowState = xlMaximized
'traitement de l'erreur si une feuille portant le même nom existe
On Error GoTo gesterror
With ActiveWorkbook
Set ws = .Worksheets.Add(, .Worksheets(.Worksheets.Count))
End With
ws.Name = CR
'Exit Sub
gesterror:
If Err.Number = 1004 Then
MsgBox " une feuille de même nom existe déja"
'message demandant confirmation de la suppression de celle-ci
Application.DisplayAlerts = True
ws.Delete
End If
ws.Move after:=Sheets(Sheets.Count)
Windows("CR022.xls").Activate
Sheets("prog").Select
Cells.Select
Selection.Copy
'Application.WindowState = xlMinimized
Windows("ClinMars.xls").Activate
' Application.WindowState = xlMaximized
Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
'¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤
'Sheets("prog").Select
' Cells.Select
' Selection.Delete Shift:=xlUp
Windows("CR022.xls").Activate
Sheets("ClinA-M").Select
Cells.Select
Selection.Copy
Sheets("prog").Select
Range("A1").Select
ActiveSheet.Paste
Sheets("data").Select
Loop
msg:
MsgBox " le traitement est fini fermer les classeurs"
'¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤
'demande si on continue avec un autre CR
'Style = vbYesNo + vbDefaultButton1
'msg = "Voulez-vous continuer avec un autre CR?"
'réponse = MsgBox(msg, Style)
'If réponse = vbYes Then GoTo etiq
'If réponse = vbNo Then
' End If
MsgBox " le programme va fermer le fichier source, répondez NON"
'fermeture du fichier de données importées
filename = InputBox("Entrer le nom du fichier importé")
Windows(filename & ".txt").Activate
ActiveWindow.Close
'fermeture du classeur de traitements
Windows("CR022.xls").Activate
ActiveWindow.Close
Windows("ClinMars.xls").Activate
Sheets("ws").Select
Range("A1").Select

End Sub

Hi Amroo

Are you sure that is it :o) I have made some modifications to your code to give you some examples to follow.

Sub MClinAM()
' MClinAM Macro
' Touche de raccourci du clavier: Ctrl+c
'"""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
'declaration des variables utilisées
Dim msgerreur
Dim mesgerror
Dim CR As Integer
Dim ws As Worksheet
Dim NM As Integer
Dim F As String

'""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
'vider le feuille import au début du prog
Application.ScreenUpdating=False
Windows("CR022.xls").Activate
Sheets("import").Cells.Clear
Sheets("prog").Cells.Clear
'invite à choisir son fichier texte à importer
UserForm3.Show
F = Sheets("import").Range("I5").Value
'ouverture du fichier du CRIH et mis en forme
'importation des données clinique du mois de janvier 2001
Workbooks.OpenText Filename:=(F), Origin _
:=xlWindows, StartRow:=2, DataType:=xlFixedWidth, FieldInfo:=Array(Array _
(0, 1), Array(4, 1), Array(8, 1), Array(9, 1), Array(17, 1), Array(55, 2), Array(78, 2), _
Array(98, 2))
'élimination des colonnes inutiles
Columns("B:C").Delete
'insertion d'une colonne de concatenation et suppression des colonnes méres.
'recopie jusqu'a fin de CR
Columns("A:A").Insert
Range("A1:A866").FormulaR1C1 = "=CONCATENATE(RC[1],RC[3])"
Range("A1:A866") = Range("A1:A866").Value
Columns("B:C").Delete
'tri par ordre alphabétique de la colonne B
ActiveSheet.UsedRange.Sort Key1:=Range("B1"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Columns("B:B").Delete
'remplacer le point de mil par "" et formatage des données texte en numérique

Columns("B:D").Replace What:=".", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Range("E1").Select
Range("E1:G807").FormulaR1C1 = "=VALUE(RC[-3])"
Range("E1:G807") = Range("E1:G807").Value
Columns("B:D").Delete
'tri par ordre croissant sur le CR
'Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Columns("A:D") = Columns("A:D").Value

As you can see, other than going to another Workbook, there is no need to select or ativate Sheets and Cells

Dave
OzGrid Business Applications

Grey matter Dave & co

Dave, I 've great pleasure to read what you have done to my code (the begining), it's more clear,and direct.
It's sometimes good to say I don't know or to have the opinion or the eye of someone which have high pedagogic capacities as you.
I think that before I get to this forum I was orphelan (is it right writting?)
Well done and best continuation.
A+mroo

Re: Grey matter Dave & co

Hi amroo

Thank you, for your kind words! I cannot help myself whenever I see the word Select in a macro :o)

Dave

OzGrid Business Applications