voila...la chose!!
Sub macro_impression_deux_pages_et_ajouter_1()
' Macro1 Macro
' Macro enregistrée le 04/10/2007 par UTILISATEUR
ActiveSheet.Unprotect ("mdp") 'deproteger la feuille'
Application.ScreenUpdating = False
Sheets("APERCU DE LA FACTURE").Visible = True
Sheets("A5").Visible = True
If Range("d9") = "" And Range("e9") = "" And Range("f9") = "" And Range("g9") = "" And Range("h9") = "" And Range("i9") = "" Then
MsgBox "nombre de chambre(s) occuppée(s)!!"
Exit Sub
End If
If Range("c2") = "" Then
MsgBox "Nom du client!"
Exit Sub
End If
If Range("c3") = "" Then
MsgBox "date d'arrivée!"
Exit Sub
End If
If Range("c4") = "" Then
MsgBox "date du départ!"
Exit Sub
End If
If Range("c5") = "" Then
MsgBox "nombre total de personne(s)!"
Exit Sub
End If
If Range("c5") * Range("c6") <> Range("j13") Then
MsgBox " vérifier le nombre total de personne(s)ou le type de chambre!"
Exit Sub
End If
If Range("d10") = "" And Range("e10") = "" And Range("f10") = "" And Range("g10") = "" And Range("h10") = "" And Range("i10") = "" Then
MsgBox "choix du tarif/hôtel!!"
Exit Sub
End If
If Range("i10") = "" And Range("i9") = 1 Then
MsgBox " la chambre n'existe pas!"
Exit Sub
End If
If Range("g10") = "" And Range("g9") = 1 Then
MsgBox " la chambre n'existe pas!"
Exit Sub
End If
If Range("d10") = "" And Range("d9") = 1 Then
MsgBox " la chambre n'existe pas!"
Exit Sub
End If
'gele l'ecran pendant le deroulement de la macro'
Application.ScreenUpdating = False
' ouvrir le dossier "factures" du mois en cour
Workbooks.Open Filename:="C:\Documents and Settings\Utilisateur\Mes documents\DOSSIERS HOTEL\SECRETARIAT\FACTURES HOTEL\factures sous excel\factures_mars_09.xls"
'revenir sur "ed de facture" et copier l'apercu de la facture
Windows("ed fact 2008-12.xls").Activate
Sheets("A5").Select
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1, Collate _
:=True
Sheets("APERCU DE LA FACTURE").Select
Range("A1:G36").Select
Selection.Copy
' creer une nouvelle feuille dans le dossier archivage des factures '
Windows("factures_mars_09.xls").Activate
Sheets("0").Select
Sheets.Add
'mise en forme de la facture enregistrée
'
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("C7").Select
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 1
Columns("A:A").ColumnWidth = 10.29
Columns("B:B").ColumnWidth = 30.29
Columns("C:C").ColumnWidth = 7.29
Columns("D
").ColumnWidth = 1.71
Columns("E:E").ColumnWidth = 12
Columns("F:F").ColumnWidth = 18
Columns("G:G").ColumnWidth = 8.57
Range("G3").Select
Application.CutCopyMode = False
Selection.NumberFormat = "m/d/yyyy"
Columns("G:G").ColumnWidth = 9.43
Range("F2").Select
Selection.NumberFormat = "d/m/yy;@"
Range("G3").Select
Selection.NumberFormat = "d/m;@"
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("E8:F11").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Range("B9:B10").Select
Selection.NumberFormat = "d/m/yy;@"
Columns("D
").ColumnWidth = 2.29
ActiveWindow.SmallScroll Down:=9
Range("C29:C30").Select
Selection.Style = "Currency"
Range("F18:F35").Select
Selection.Style = "Currency"
ActiveWindow.SmallScroll Down:=-24
Windows("ed fact 2008-12.xls").Activate
Range("F3").Select
Windows("factures_mars_09.xls").Activate
Range("D1").Select
Selection.ClearContents
Range("A9:B11").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Range("E8:F11").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
ActiveWindow.SmallScroll Down:=3
Range("B13:F35").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Range("E15:F34,C29:C30").Select
Range("C29").Activate
Selection.Style = "Currency"
Selection.Style = "Currency"
Selection.NumberFormat = "#,##0.00 €"
End With
With Range("B9:B10").Select
Selection.NumberFormat = "[$-F800]dddd, mmmm dd, yyyy"
Range("B11").Select
Selection.NumberFormat = "General"
Range("E3").Select
ActiveCell.FormulaR1C1 = "FACTURE N°"
Range("F3").Select
End With
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Range("B11").Select
Selection.NumberFormat = "General"" nuitée(s)"""
Range("G3").Select
Selection.ClearContents
Range("F2").Select
End With
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("B:B").ColumnWidth = 36.29
Columns("B:B").ColumnWidth = 38.14
Columns("B:B").ColumnWidth = 41.71
Columns("B:B").ColumnWidth = 40.71
Range("B9:B11").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Range("C5").Select
Columns("F:F").ColumnWidth = 15.29
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 3
Columns("A:A").ColumnWidth = 8.43
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 1
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 1
End With
With Range("E8:F11").Select
Selection.Font.Bold = True
Range("A9:B11").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("A1:B7").Select
Selection.Font.Bold = True
Range("E2:F3").Select
Selection.Font.Bold = True
Cells.Select
Selection.Font.Bold = False
Selection.Font.Bold = True
Range("B4").Select
Columns("B:B").ColumnWidth = 44
End With
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("B5").Select
Columns("B:B").ColumnWidth = 32.86
Range("B27").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("B5").Select
Columns("B:B").ColumnWidth = 32
Range("B27").Select
ActiveCell.FormulaR1C1 = _
"remise exeptionnelle (sur hebergement) :"
With ActiveCell.Characters(Start:=1, Length:=54).Font
.Name = "Arial"
.FontStyle = "Gras"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("B27").Select
ActiveCell.FormulaR1C1 = _
"remise exeptionnelle (sur hebergement) :"
With ActiveCell.Characters(Start:=1, Length:=61).Font
.Name = "Arial"
.FontStyle = "Gras"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("E8:F11").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
'nommer la feuille crée par le n° de la facture'
With Range("B1").Select
ActiveSheet.Name = Range("f3").Value
'on enregistre le dossier factures et on ferme'
With Windows("factures_mars_09.xls").Activate
ActiveWorkbook.Save
ActiveWindow.Close
'on retourne sur saisie des infos sur ed de facture'
Windows("ed fact 2008-12.xls").Activate
Range("B8").Select
Sheets("SAISIE DES INFORMATIONS").Select
Range("C2:E2").Select
End With
'incrementer de 1'
With Sheets("SAISIE DES INFORMATIONS").Select
Range("C2:E2").Select
Sheets("apercu de la facture").Range("f3").Value = Sheets("apercu de la facture").Range("f3").Value + 1
'et remetre à zero la feulle de saisie'
Range("C5").Select
ActiveCell.FormulaR1C1 = ""
Range("C2").Select
ActiveCell.FormulaR1C1 = ""
Range("C3").Select
ActiveCell.FormulaR1C1 = ""
Range("C4").Select
ActiveCell.FormulaR1C1 = ""
Range("D9").Select
ActiveCell.FormulaR1C1 = ""
Range("E9").Select
ActiveCell.FormulaR1C1 = ""
Range("F9").Select
ActiveCell.FormulaR1C1 = ""
Range("G9").Select
ActiveCell.FormulaR1C1 = ""
Range("H9").Select
ActiveCell.FormulaR1C1 = ""
Range("I9").Select
ActiveCell.FormulaR1C1 = ""
Range("J9").Select
ActiveCell.FormulaR1C1 = ""
Range("H18").Select
ActiveCell.FormulaR1C1 = ""
Range("G18").Select
ActiveCell.FormulaR1C1 = ""
Range("E18").Select
ActiveCell.FormulaR1C1 = ""
Range("D17").Select
ActiveCell.FormulaR1C1 = ""
Range("j16").Select
ActiveCell.FormulaR1C1 = ""
Range("C2").Select
Range("D10:I10").Select
Selection.ClearContents
Range("a1").Select
End With
Sheets("APERCU DE LA FACTURE").Visible = False
Sheets("A5").Visible = False
Application.ScreenUpdating = True
ActiveSheet.Protect ("mdp") 'deproteger la feuille'
End With
End Sub