Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

how to run recorded macro without showing processing excel sheets

1 view
Skip to first unread message

christine

unread,
Jan 3, 2004, 6:48:55 PM1/3/04
to

Can someone help me plzzzzzzz.
I have recorded some macros that whenever i run them i
have to run them through an excel sheet. And it display
all the excel sheets. About 10 separate sheets.I would
like to
know how to run the macros without showing all of the
processing excel sheets. Without user viewing the actual
processes.
Thanks in advance

Please this macro does not work:

at the beginning:

Application.ScreenUpdating = False


at the end

application.ScreenUpdating = True


JMay

unread,
Jan 3, 2004, 7:00:27 PM1/3/04
to
Insert
Application.ScreenUpdating = False
on second line of your code and
Application.ScreenUpdating = True
one line before your EndSub statement


"christine" <chri...@yahoo.com> wrote in message
news:05d401c3d254$25ebc4c0$a001...@phx.gbl...

JMay

unread,
Jan 3, 2004, 7:02:00 PM1/3/04
to
Sorry; I answered before reading the all of your Q...


"JMay" <jm...@cox.net> wrote in message
news:kIIJb.66728$hf1.48117@lakeread06...

Tom Ogilvy

unread,
Jan 3, 2004, 7:01:43 PM1/3/04
to
It works for everybody else.

from excel vba help on screenupdating:

Turn screen updating off to speed up your macro code. You won't be able to
see what the macro is doing, but it will run faster.


sounds an awful lot like what you are asking, No?

--
Regards,
Tom Ogilvy

christine <chri...@yahoo.com> wrote in message
news:05d401c3d254$25ebc4c0$a001...@phx.gbl...
>

Tom Ogilvy

unread,
Jan 3, 2004, 7:06:47 PM1/3/04
to
Maybe you can find you problem here:

http://tinyurl.com/2kqs4

--
Regards,
Tom Ogilvy


christine <chri...@yahoo.com> wrote in message
news:05d401c3d254$25ebc4c0$a001...@phx.gbl...
>

christine

unread,
Jan 3, 2004, 9:50:07 PM1/3/04
to
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

>.
>

Ken Cowen

unread,
Jan 3, 2004, 10:33:45 PM1/3/04
to
Is there a chance that your macro runs another macro that sets
screenupdating back to true somewhere in the middle of the procedure? The
usual guidance is to reset screenupdating back to true at the end of the
procedure, but usually I prefer not to do that because it happens
automatically when processes all complete, and often I will later call a
procedure from another procedure and the second procedure will set
screenupdating to true if I have previously followed the convention of
setting screenupdating back to true and as it turns out, the could be
premature.

Ken


"Tom Ogilvy" <twog...@msn.com> wrote in message
news:vvem0hg...@news.supernews.com...

Tom Ogilvy

unread,
Jan 4, 2004, 12:17:33 AM1/4/04
to
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 <chri...@yahoo.com> wrote in message

news:039001c3d26d$769aa3a0$a301...@phx.gbl...

anon...@discussions.microsoft.com

unread,
Jan 4, 2004, 3:28:05 PM1/4/04
to
Ken, i think you are right. I have posted the codes. what
do i have to delete from those codes?
>.
>

Tom Ogilvy

unread,
Jan 4, 2004, 4:10:37 PM1/4/04
to
You posted your code - it doesn't run any other macros - why do you think it
might?

--
Regards,
Tom Ogilvy


<anon...@discussions.microsoft.com> wrote in message
news:012201c3d301$41ea2d80$a401...@phx.gbl...

David McRitchie

unread,
Jan 4, 2004, 5:57:47 PM1/4/04
to
I agree, out of 10,400 hits there must be at least one that is on target <grin>..

"Tom Ogilvy" <twog...@msn.com> wrote in message news:vvem9up...@news.supernews.com...

0 new messages