|
HOW to run marco without displaying the workbooks
Thanks tom. This is the code i recorded. It opens the
wookbook, test, ken and hector. I do not want these work book to open when the macro runs. thanks in advance, you have been helpfull. Sub test() Application.ScreenUpdating = False ' test Macro ' Macro recorded 1/3/2004 by Christine ' ' ChDir "C:\Documents and Settings\Desktop\Marco" Workbooks.OpenText Filename:= _ "C:\Documents and Settings\Desktop\Marco\Hector.tvr", Origin _ :=437, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 9), _ Array(4, 1), Array(14, 1), Array(36, 1), Array (58, 1)), TrailingMinusNumbers:=True Rows("1:13").Select Selection.Delete Shift:=xlUp Columns("A:A").EntireColumn.AutoFit Columns("B:B").EntireColumn.AutoFit Columns("C:C").EntireColumn.AutoFit Columns("D:D").EntireColumn.AutoFit Columns("A:D").Select Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="}" Selection.ClearContents Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="_________" Selection.ClearContents Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="--------" Selection.ClearContents Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="Date: 12/" Selection.ClearContents Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="CT: 19 W" Selection.ClearContents Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="Report Ac" Selection.ClearContents Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="Managemen" Selection.ClearContents Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="Saturday" Selection.ClearContents Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="Sorted by" Range("A57:B378").Select Selection.ClearContents Columns("A:D").Select Selection.AutoFilter Field:=1, Criteria1:="Selected" Selection.ClearContents Columns("A:D").Select Selection.AutoFilter Selection.AutoFilter Field:=3, Criteria1:="=*rank*", Operator:=xlAnd Range("A3:B324").Select Selection.Font.ColorIndex = 2 With Selection.Interior .ColorIndex = 3 .Pattern = xlSolid End With Selection.Font.Bold = True With Selection.Font .Name = "Arial" .Size = 11 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = 2 End With Range("C3:C324").Select Selection.ClearContents Selection.AutoFilter Field:=1 Columns("A:D").Select ActiveSheet.ShowAllData Selection.AutoFilter Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="Date" Range("A4:D325").Select Selection.Font.ColorIndex = 2 With Selection.Interior .ColorIndex = 41 .Pattern = xlSolid End With Selection.Font.Bold = True Selection.AutoFilter Field:=1, Criteria1:="=" Selection.AutoFilter Field:=2, Criteria1:="=" Selection.AutoFilter Field:=3, Criteria1:="=" Selection.AutoFilter Field:=4, Criteria1:="=" Rows("5:376").Select Selection.Delete Shift:=xlUp ActiveSheet.ShowAllData Columns("A:D").Select Selection.AutoFilter Rows("1:1").Select Selection.Delete Shift:=xlUp Range("A1").Select ActiveCell.FormulaR1C1 = "Date" Range("B1").Select ActiveCell.FormulaR1C1 = "Schedules" Range("C1").Select ActiveCell.FormulaR1C1 = "Exception " Range("D1").Select ActiveCell.FormulaR1C1 = "Time" Range("A1:D1").Select Selection.Font.ColorIndex = 2 With Selection.Interior .ColorIndex = 3 .Pattern = xlSolid End With Selection.Font.Bold = True With Selection.Font .Name = "Arial" .Size = 14 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = 2 End With Range("A1:D256").Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlThick .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlThick .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThick .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlThick .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("B10").Select ActiveWorkbook.SaveAs Filename:= _ "C:\Documents and Settings\Desktop\Marco\Hector.htm", _ FileFormat:=xlHtml, ReadOnlyRecommended:=False, CreateBackup:=False Selection.Delete Shift:=xlToLeft Workbooks.OpenText Filename:= _ "C:\Documents and Settings\Desktop\Marco\Ken.tvr", Origin:= _ 437, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 9), _ Array(4, 1), Array(14, 1), Array(36, 1), Array (57, 1), Array(64, 1)), _ TrailingMinusNumbers:=True Rows("1:13").Select Selection.Delete Shift:=xlUp Columns("A:A").EntireColumn.AutoFit Columns("B:B").EntireColumn.AutoFit Columns("C:C").EntireColumn.AutoFit Columns("D:D").EntireColumn.AutoFit Columns("A:E").Select Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="}" Selection.ClearContents Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="_________" Selection.ClearContents Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="--------" Selection.ClearContents Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="Date: 12/" Selection.ClearContents Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="CT: 19 W" Selection.ClearContents Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="Managemen" Selection.ClearContents Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="Report Ac" Selection.ClearContents Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="Saturday" Selection.ClearContents Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="Sorted by" Selection.ClearContents Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="Selected" Selection.ClearContents Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="=" Selection.AutoFilter Field:=2, Criteria1:="=" Selection.AutoFilter Field:=3, Criteria1:="=" Selection.AutoFilter Field:=4, Criteria1:="=" Selection.AutoFilter Field:=5, Criteria1:="=" Rows("2:460").Select Range("A460").Activate Selection.Delete Shift:=xlUp ActiveSheet.ShowAllData Selection.AutoFilter Range("B406").Select ActiveWindow.LargeScroll Down:=-10 ActiveWindow.ScrollRow = 2 ActiveWindow.ScrollRow = 1 Columns("A:E").Select Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="Date" Range("A3:E303").Select Selection.Font.ColorIndex = 2 With Selection.Interior .ColorIndex = 41 .Pattern = xlSolid End With Selection.Font.Bold = True Selection.AutoFilter Field:=3 ActiveSheet.ShowAllData Columns("A:E").Select Selection.AutoFilter Field:=3, Criteria1:="=*rank*", Operator:=xlAnd Range("A2:B302").Select Selection.Font.ColorIndex = 2 With Selection.Interior .ColorIndex = 3 .Pattern = xlSolid End With Selection.Font.Bold = True With Selection.Font .Name = "Arial" .Size = 11 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = 2 End With Range("C2:C302").Select Selection.ClearContents Columns("A:E").Select Selection.AutoFilter Selection.AutoFilter Selection.AutoFilter Columns("C:C").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _ Formula1:="=""vacation""" With Selection.FormatConditions(1).Font .Bold = True .Italic = True .ColorIndex = 1 End With Selection.FormatConditions(1).Interior.ColorIndex = 6 Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _ Formula1:="=""personal day off""" With Selection.FormatConditions(2).Font .Bold = True .Italic = True .ColorIndex = 2 End With Selection.FormatConditions(2).Interior.ColorIndex = 9 Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _ Formula1:="=""e-time""" With Selection.FormatConditions(3).Font .Bold = True .Italic = True .ColorIndex = 2 End With Selection.FormatConditions(3).Interior.ColorIndex = 10 Range("A1").Select ActiveCell.FormulaR1C1 = "Date" Range("B1").Select ActiveCell.FormulaR1C1 = "Schedules" Range("C1").Select ActiveCell.FormulaR1C1 = "Exception" Range("D1").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _ Formula1:="=""vacation""" With Selection.FormatConditions(1).Font .Bold = True .Italic = True .ColorIndex = 1 End With Selection.FormatConditions(1).Interior.ColorIndex = 6 Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _ Formula1:="=""personal day off""" With Selection.FormatConditions(2).Font .Bold = True .Italic = True .ColorIndex = 2 End With Selection.FormatConditions(2).Interior.ColorIndex = 9 Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _ Formula1:="=""e-time""" With Selection.FormatConditions(3).Font .Bold = True .Italic = True .ColorIndex = 2 End With Selection.FormatConditions(3).Interior.ColorIndex = 10 ActiveCell.FormulaR1C1 = "Start" Range("E1").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _ Formula1:="=""vacation""" With Selection.FormatConditions(1).Font .Bold = True .Italic = True .ColorIndex = 1 End With Selection.FormatConditions(1).Interior.ColorIndex = 6 Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _ Formula1:="=""personal day off""" With Selection.FormatConditions(2).Font .Bold = True .Italic = True .ColorIndex = 2 End With Selection.FormatConditions(2).Interior.ColorIndex = 9 Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _ Formula1:="=""e-time""" With Selection.FormatConditions(3).Font .Bold = True .Italic = True .ColorIndex = 2 End With Selection.FormatConditions(3).Interior.ColorIndex = 10 ActiveCell.FormulaR1C1 = "Stop" Range("A1:E1").Select Selection.Font.ColorIndex = 2 With Selection.Interior .ColorIndex = 3 .Pattern = xlSolid End With Selection.Font.Bold = True With Selection.Font .Name = "Arial" .Size = 14 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = 2 End With Range("A1:E324").Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlThick .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlThick .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThick .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlThick .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("G19").Select ActiveWorkbook.SaveAs Filename:= _ "C:\Documents and Settings\Desktop\Marco\Ken.htm", FileFormat _ :=xlHtml, ReadOnlyRecommended:=False, CreateBackup:=False Application.ScreenUpdating = True End Sub |
HOW to run marco without displaying the workbooks
I ran your macro and it worked fine for me. All the activity in your macro
was hidden. Since you didn't close the workbooks in your code, the two files Hector and Ken were still visible, but other than that, no activity was shown. -- Regards, Tom Ogilvy christine wrote in message ... Thanks tom. This is the code i recorded. It opens the wookbook, test, ken and hector. I do not want these work book to open when the macro runs. thanks in advance, you have been helpfull. Sub test() Application.ScreenUpdating = False ' test Macro ' Macro recorded 1/3/2004 by Christine ' ' ChDir "C:\Documents and Settings\Desktop\Marco" Workbooks.OpenText Filename:= _ "C:\Documents and Settings\Desktop\Marco\Hector.tvr", Origin _ :=437, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 9), _ Array(4, 1), Array(14, 1), Array(36, 1), Array (58, 1)), TrailingMinusNumbers:=True Rows("1:13").Select Selection.Delete Shift:=xlUp Columns("A:A").EntireColumn.AutoFit Columns("B:B").EntireColumn.AutoFit Columns("C:C").EntireColumn.AutoFit Columns("D:D").EntireColumn.AutoFit Columns("A:D").Select Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="}" Selection.ClearContents Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="_________" Selection.ClearContents Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="--------" Selection.ClearContents Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="Date: 12/" Selection.ClearContents Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="CT: 19 W" Selection.ClearContents Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="Report Ac" Selection.ClearContents Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="Managemen" Selection.ClearContents Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="Saturday" Selection.ClearContents Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="Sorted by" Range("A57:B378").Select Selection.ClearContents Columns("A:D").Select Selection.AutoFilter Field:=1, Criteria1:="Selected" Selection.ClearContents Columns("A:D").Select Selection.AutoFilter Selection.AutoFilter Field:=3, Criteria1:="=*rank*", Operator:=xlAnd Range("A3:B324").Select Selection.Font.ColorIndex = 2 With Selection.Interior .ColorIndex = 3 .Pattern = xlSolid End With Selection.Font.Bold = True With Selection.Font .Name = "Arial" .Size = 11 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = 2 End With Range("C3:C324").Select Selection.ClearContents Selection.AutoFilter Field:=1 Columns("A:D").Select ActiveSheet.ShowAllData Selection.AutoFilter Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="Date" Range("A4:D325").Select Selection.Font.ColorIndex = 2 With Selection.Interior .ColorIndex = 41 .Pattern = xlSolid End With Selection.Font.Bold = True Selection.AutoFilter Field:=1, Criteria1:="=" Selection.AutoFilter Field:=2, Criteria1:="=" Selection.AutoFilter Field:=3, Criteria1:="=" Selection.AutoFilter Field:=4, Criteria1:="=" Rows("5:376").Select Selection.Delete Shift:=xlUp ActiveSheet.ShowAllData Columns("A:D").Select Selection.AutoFilter Rows("1:1").Select Selection.Delete Shift:=xlUp Range("A1").Select ActiveCell.FormulaR1C1 = "Date" Range("B1").Select ActiveCell.FormulaR1C1 = "Schedules" Range("C1").Select ActiveCell.FormulaR1C1 = "Exception " Range("D1").Select ActiveCell.FormulaR1C1 = "Time" Range("A1:D1").Select Selection.Font.ColorIndex = 2 With Selection.Interior .ColorIndex = 3 .Pattern = xlSolid End With Selection.Font.Bold = True With Selection.Font .Name = "Arial" .Size = 14 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = 2 End With Range("A1:D256").Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlThick .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlThick .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThick .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlThick .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("B10").Select ActiveWorkbook.SaveAs Filename:= _ "C:\Documents and Settings\Desktop\Marco\Hector.htm", _ FileFormat:=xlHtml, ReadOnlyRecommended:=False, CreateBackup:=False Selection.Delete Shift:=xlToLeft Workbooks.OpenText Filename:= _ "C:\Documents and Settings\Desktop\Marco\Ken.tvr", Origin:= _ 437, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 9), _ Array(4, 1), Array(14, 1), Array(36, 1), Array (57, 1), Array(64, 1)), _ TrailingMinusNumbers:=True Rows("1:13").Select Selection.Delete Shift:=xlUp Columns("A:A").EntireColumn.AutoFit Columns("B:B").EntireColumn.AutoFit Columns("C:C").EntireColumn.AutoFit Columns("D:D").EntireColumn.AutoFit Columns("A:E").Select Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="}" Selection.ClearContents Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="_________" Selection.ClearContents Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="--------" Selection.ClearContents Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="Date: 12/" Selection.ClearContents Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="CT: 19 W" Selection.ClearContents Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="Managemen" Selection.ClearContents Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="Report Ac" Selection.ClearContents Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="Saturday" Selection.ClearContents Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="Sorted by" Selection.ClearContents Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="Selected" Selection.ClearContents Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="=" Selection.AutoFilter Field:=2, Criteria1:="=" Selection.AutoFilter Field:=3, Criteria1:="=" Selection.AutoFilter Field:=4, Criteria1:="=" Selection.AutoFilter Field:=5, Criteria1:="=" Rows("2:460").Select Range("A460").Activate Selection.Delete Shift:=xlUp ActiveSheet.ShowAllData Selection.AutoFilter Range("B406").Select ActiveWindow.LargeScroll Down:=-10 ActiveWindow.ScrollRow = 2 ActiveWindow.ScrollRow = 1 Columns("A:E").Select Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="Date" Range("A3:E303").Select Selection.Font.ColorIndex = 2 With Selection.Interior .ColorIndex = 41 .Pattern = xlSolid End With Selection.Font.Bold = True Selection.AutoFilter Field:=3 ActiveSheet.ShowAllData Columns("A:E").Select Selection.AutoFilter Field:=3, Criteria1:="=*rank*", Operator:=xlAnd Range("A2:B302").Select Selection.Font.ColorIndex = 2 With Selection.Interior .ColorIndex = 3 .Pattern = xlSolid End With Selection.Font.Bold = True With Selection.Font .Name = "Arial" .Size = 11 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = 2 End With Range("C2:C302").Select Selection.ClearContents Columns("A:E").Select Selection.AutoFilter Selection.AutoFilter Selection.AutoFilter Columns("C:C").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _ Formula1:="=""vacation""" With Selection.FormatConditions(1).Font .Bold = True .Italic = True .ColorIndex = 1 End With Selection.FormatConditions(1).Interior.ColorIndex = 6 Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _ Formula1:="=""personal day off""" With Selection.FormatConditions(2).Font .Bold = True .Italic = True .ColorIndex = 2 End With Selection.FormatConditions(2).Interior.ColorIndex = 9 Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _ Formula1:="=""e-time""" With Selection.FormatConditions(3).Font .Bold = True .Italic = True .ColorIndex = 2 End With Selection.FormatConditions(3).Interior.ColorIndex = 10 Range("A1").Select ActiveCell.FormulaR1C1 = "Date" Range("B1").Select ActiveCell.FormulaR1C1 = "Schedules" Range("C1").Select ActiveCell.FormulaR1C1 = "Exception" Range("D1").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _ Formula1:="=""vacation""" With Selection.FormatConditions(1).Font .Bold = True .Italic = True .ColorIndex = 1 End With Selection.FormatConditions(1).Interior.ColorIndex = 6 Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _ Formula1:="=""personal day off""" With Selection.FormatConditions(2).Font .Bold = True .Italic = True .ColorIndex = 2 End With Selection.FormatConditions(2).Interior.ColorIndex = 9 Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _ Formula1:="=""e-time""" With Selection.FormatConditions(3).Font .Bold = True .Italic = True .ColorIndex = 2 End With Selection.FormatConditions(3).Interior.ColorIndex = 10 ActiveCell.FormulaR1C1 = "Start" Range("E1").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _ Formula1:="=""vacation""" With Selection.FormatConditions(1).Font .Bold = True .Italic = True .ColorIndex = 1 End With Selection.FormatConditions(1).Interior.ColorIndex = 6 Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _ Formula1:="=""personal day off""" With Selection.FormatConditions(2).Font .Bold = True .Italic = True .ColorIndex = 2 End With Selection.FormatConditions(2).Interior.ColorIndex = 9 Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _ Formula1:="=""e-time""" With Selection.FormatConditions(3).Font .Bold = True .Italic = True .ColorIndex = 2 End With Selection.FormatConditions(3).Interior.ColorIndex = 10 ActiveCell.FormulaR1C1 = "Stop" Range("A1:E1").Select Selection.Font.ColorIndex = 2 With Selection.Interior .ColorIndex = 3 .Pattern = xlSolid End With Selection.Font.Bold = True With Selection.Font .Name = "Arial" .Size = 14 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = 2 End With Range("A1:E324").Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlThick .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlThick .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThick .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlThick .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("G19").Select ActiveWorkbook.SaveAs Filename:= _ "C:\Documents and Settings\Desktop\Marco\Ken.htm", FileFormat _ :=xlHtml, ReadOnlyRecommended:=False, CreateBackup:=False Application.ScreenUpdating = True End Sub |
HOW to run marco without displaying the workbooks
|
HOW to run marco without displaying the workbooks
But i needed that . That is what delete the first work in
order for me to import the next text file -----Original Message----- A suggestion would be to get rid of all of the selects that are unnecessary. Example Rows("1:13").Delete Shift:=xlUp with Columns("A:D") .EntireColumn.AutoFit .AutoFilter Field:=1, Criteria1:="}" .ClearContents .AutoFilter Field:=1, Criteria1:="_________" end with -- Don Guillett SalesAid Software "christine" wrote in message ... Thanks tom. This is the code i recorded. It opens the wookbook, test, ken and hector. I do not want these work book to open when the macro runs. thanks in advance, you have been helpfull. Sub test() Application.ScreenUpdating = False ' test Macro ' Macro recorded 1/3/2004 by Christine ' ' ChDir "C:\Documents and Settings\Desktop\Marco" Workbooks.OpenText Filename:= _ "C:\Documents and Settings\Desktop\Marco\Hector.tvr", Origin _ :=437, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 9), _ Array(4, 1), Array(14, 1), Array(36, 1), Array (58, 1)), TrailingMinusNumbers:=True Rows("1:13").Select Selection.Delete Shift:=xlUp Columns("A:A").EntireColumn.AutoFit Columns("B:B").EntireColumn.AutoFit Columns("C:C").EntireColumn.AutoFit Columns("D:D").EntireColumn.AutoFit Columns("A:D").Select Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="}" Selection.ClearContents Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="_________" Selection.ClearContents Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="-------- " Selection.ClearContents Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="Date: 12/" Selection.ClearContents Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="CT: 19 W" Selection.ClearContents Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="Report Ac" Selection.ClearContents Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="Managemen" Selection.ClearContents Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="Saturday" Selection.ClearContents Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="Sorted by" Range("A57:B378").Select Selection.ClearContents Columns("A:D").Select Selection.AutoFilter Field:=1, Criteria1:="Selected" Selection.ClearContents Columns("A:D").Select Selection.AutoFilter Selection.AutoFilter Field:=3, Criteria1:="=*rank*", Operator:=xlAnd Range("A3:B324").Select Selection.Font.ColorIndex = 2 With Selection.Interior .ColorIndex = 3 .Pattern = xlSolid End With Selection.Font.Bold = True With Selection.Font .Name = "Arial" .Size = 11 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = 2 End With Range("C3:C324").Select Selection.ClearContents Selection.AutoFilter Field:=1 Columns("A:D").Select ActiveSheet.ShowAllData Selection.AutoFilter Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="Date" Range("A4:D325").Select Selection.Font.ColorIndex = 2 With Selection.Interior .ColorIndex = 41 .Pattern = xlSolid End With Selection.Font.Bold = True Selection.AutoFilter Field:=1, Criteria1:="=" Selection.AutoFilter Field:=2, Criteria1:="=" Selection.AutoFilter Field:=3, Criteria1:="=" Selection.AutoFilter Field:=4, Criteria1:="=" Rows("5:376").Select Selection.Delete Shift:=xlUp ActiveSheet.ShowAllData Columns("A:D").Select Selection.AutoFilter Rows("1:1").Select Selection.Delete Shift:=xlUp Range("A1").Select ActiveCell.FormulaR1C1 = "Date" Range("B1").Select ActiveCell.FormulaR1C1 = "Schedules" Range("C1").Select ActiveCell.FormulaR1C1 = "Exception " Range("D1").Select ActiveCell.FormulaR1C1 = "Time" Range("A1:D1").Select Selection.Font.ColorIndex = 2 With Selection.Interior .ColorIndex = 3 .Pattern = xlSolid End With Selection.Font.Bold = True With Selection.Font .Name = "Arial" .Size = 14 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = 2 End With Range("A1:D256").Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlThick .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlThick .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThick .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlThick .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("B10").Select ActiveWorkbook.SaveAs Filename:= _ "C:\Documents and Settings\Desktop\Marco\Hector.htm", _ FileFormat:=xlHtml, ReadOnlyRecommended:=False, CreateBackup:=False Selection.Delete Shift:=xlToLeft Workbooks.OpenText Filename:= _ "C:\Documents and Settings\Desktop\Marco\Ken.tvr", Origin:= _ 437, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 9), _ Array(4, 1), Array(14, 1), Array(36, 1), Array (57, 1), Array(64, 1)), _ TrailingMinusNumbers:=True Rows("1:13").Select Selection.Delete Shift:=xlUp Columns("A:A").EntireColumn.AutoFit Columns("B:B").EntireColumn.AutoFit Columns("C:C").EntireColumn.AutoFit Columns("D:D").EntireColumn.AutoFit Columns("A:E").Select Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="}" Selection.ClearContents Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="_________" Selection.ClearContents Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="-------- " Selection.ClearContents Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="Date: 12/" Selection.ClearContents Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="CT: 19 W" Selection.ClearContents Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="Managemen" Selection.ClearContents Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="Report Ac" Selection.ClearContents Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="Saturday" Selection.ClearContents Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="Sorted by" Selection.ClearContents Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="Selected" Selection.ClearContents Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="=" Selection.AutoFilter Field:=2, Criteria1:="=" Selection.AutoFilter Field:=3, Criteria1:="=" Selection.AutoFilter Field:=4, Criteria1:="=" Selection.AutoFilter Field:=5, Criteria1:="=" Rows("2:460").Select Range("A460").Activate Selection.Delete Shift:=xlUp ActiveSheet.ShowAllData Selection.AutoFilter Range("B406").Select ActiveWindow.LargeScroll Down:=-10 ActiveWindow.ScrollRow = 2 ActiveWindow.ScrollRow = 1 Columns("A:E").Select Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="Date" Range("A3:E303").Select Selection.Font.ColorIndex = 2 With Selection.Interior .ColorIndex = 41 .Pattern = xlSolid End With Selection.Font.Bold = True Selection.AutoFilter Field:=3 ActiveSheet.ShowAllData Columns("A:E").Select Selection.AutoFilter Field:=3, Criteria1:="=*rank*", Operator:=xlAnd Range("A2:B302").Select Selection.Font.ColorIndex = 2 With Selection.Interior .ColorIndex = 3 .Pattern = xlSolid End With Selection.Font.Bold = True With Selection.Font .Name = "Arial" .Size = 11 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = 2 End With Range("C2:C302").Select Selection.ClearContents Columns("A:E").Select Selection.AutoFilter Selection.AutoFilter Selection.AutoFilter Columns("C:C").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _ Formula1:="=""vacation""" With Selection.FormatConditions(1).Font .Bold = True .Italic = True .ColorIndex = 1 End With Selection.FormatConditions(1).Interior.ColorIndex = 6 Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _ Formula1:="=""personal day off""" With Selection.FormatConditions(2).Font .Bold = True .Italic = True .ColorIndex = 2 End With Selection.FormatConditions(2).Interior.ColorIndex = 9 Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _ Formula1:="=""e-time""" With Selection.FormatConditions(3).Font .Bold = True .Italic = True .ColorIndex = 2 End With Selection.FormatConditions(3).Interior.ColorIndex = 10 Range("A1").Select ActiveCell.FormulaR1C1 = "Date" Range("B1").Select ActiveCell.FormulaR1C1 = "Schedules" Range("C1").Select ActiveCell.FormulaR1C1 = "Exception" Range("D1").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _ Formula1:="=""vacation""" With Selection.FormatConditions(1).Font .Bold = True .Italic = True .ColorIndex = 1 End With Selection.FormatConditions(1).Interior.ColorIndex = 6 Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _ Formula1:="=""personal day off""" With Selection.FormatConditions(2).Font .Bold = True .Italic = True .ColorIndex = 2 End With Selection.FormatConditions(2).Interior.ColorIndex = 9 Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _ Formula1:="=""e-time""" With Selection.FormatConditions(3).Font .Bold = True .Italic = True .ColorIndex = 2 End With Selection.FormatConditions(3).Interior.ColorIndex = 10 ActiveCell.FormulaR1C1 = "Start" Range("E1").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _ Formula1:="=""vacation""" With Selection.FormatConditions(1).Font .Bold = True .Italic = True .ColorIndex = 1 End With Selection.FormatConditions(1).Interior.ColorIndex = 6 Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _ Formula1:="=""personal day off""" With Selection.FormatConditions(2).Font .Bold = True .Italic = True .ColorIndex = 2 End With Selection.FormatConditions(2).Interior.ColorIndex = 9 Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _ Formula1:="=""e-time""" With Selection.FormatConditions(3).Font .Bold = True .Italic = True .ColorIndex = 2 End With Selection.FormatConditions(3).Interior.ColorIndex = 10 ActiveCell.FormulaR1C1 = "Stop" Range("A1:E1").Select Selection.Font.ColorIndex = 2 With Selection.Interior .ColorIndex = 3 .Pattern = xlSolid End With Selection.Font.Bold = True With Selection.Font .Name = "Arial" .Size = 14 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = 2 End With Range("A1:E324").Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlThick .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlThick .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThick .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlThick .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("G19").Select ActiveWorkbook.SaveAs Filename:= _ "C:\Documents and Settings\Desktop\Marco\Ken.htm", FileFormat _ :=xlHtml, ReadOnlyRecommended:=False, CreateBackup:=False Application.ScreenUpdating = True End Sub . |
| All times are GMT +1. The time now is 06:03 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com