App.screenupdating

[Steph]

Is there any way I can prevent the screen from updating/"blipping" when I
run code? I thought application.screenupdating=false would do it. But my
code below adds a new workbook, copies some data to it, manipulates it, etc.
And as it is doing that, my screen "blips", and I have App.screenupdating
set to false. Is there a way to maybe set focus to the main worksheet so
the end user won't see and blips of the new workbook being added and
manipulated?

Sub Preparetxt()

Dim ans As String
Dim c As Range
Dim iLastRow As Long
Dim iLastRowPO As Long
Dim iLastCol As Long
Dim iCol As Long
Dim cCols As Long
Dim i As Long, j As Long
Dim fAll As Boolean
Dim sTemp

Application.ScreenUpdating = False

Worksheets("HR DB").Range("A5:BJ" & Range("B1499").End(xlUp).Row).Copy
Workbooks.Add
ActiveWorkbook.Worksheets(1).Range("A1").PasteSpecial Paste:=xlPasteValues

'Delete Inactive Status
ans = "Inactive"
With Columns(12)
Do
Set c = .Find(ans, LookIn:=xlValues)
If Not c Is Nothing Then
c.EntireRow.Delete
End If
Loop While Not c Is Nothing
End With

Columns("A:A").Delete Shift:=xlToLeft
Columns("C:C").Cut
Columns("A:A").Insert Shift:=xlToRight
Columns("C:K").Delete Shift:=xlToLeft

iLastRowPO = Workbooks("Contractor Master4.xls").Worksheets("PO
Table").Cells(Rows.Count, "D").End(xlUp).Row
For i = 5 To iLastRowPO
If Not IsEmpty(Workbooks("Contractor Master4.xls").Worksheets("PO
Table").Cells(i, "D")) Then
sTemp = sTemp & "|" & Workbooks("Contractor
Master4.xls").Worksheets("PO Table").Cells(i, "D").Value
End If
Next i

iLastRow = Cells(Rows.Count, "B").End(xlUp).Row
For i = 1 To iLastRow
fAll = Cells(i, "C").Value = "--All--"
iLastCol = Cells(i, Columns.Count).End(xlToLeft).Column
If fAll Then
For j = 2 To iLastCol
If Cells(i, j).Value <> "" Then
Cells(i, 1).Value = Cells(i, 1).Value & "|" & Cells(i,
j).Value
End If
Next j
Cells(i, 1).Value = Cells(i, 1).Value & sTemp
Else
For j = 2 To iLastCol
If Cells(i, j).Value <> "" Then
Cells(i, 1).Value = Cells(i, 1).Value & "|" & Cells(i,
j).Value
End If
Next j
End If
cCols = IIf(iLastCol > 1, iLastCol - 1, 1)
Cells(i, 2).Resize(, cCols).ClearContents
Next i

Columns("A:A").Replace What:="|--All--", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Columns("A:A").HorizontalAlignment = xlLeft
Application.ScreenUpdating = True
End Sub

[Hfly]

I use that religiously in my macros, and I've noticed that although it won't
show every single thing that happens, it has never hidden the opening of
other workbooks and still displays them in the taskbar on the bottom of
Windows. You see them open, then you see them close.

Hfly

"Steph" wrote:

> Is there any way I can prevent the screen from updating/"blipping" when I
> run code? I thought application.screenupdating=false would do it. But my
> code below adds a new workbook, copies some data to it, manipulates it, etc.
> And as it is doing that, my screen "blips", and I have App.screenupdating
> set to false. Is there a way to maybe set focus to the main worksheet so
> the end user won't see and blips of the new workbook being added and
> manipulated?
>
> Sub Preparetxt()
>
> Dim ans As String
> Dim c As Range
> Dim iLastRow As Long
> Dim iLastRowPO As Long
> Dim iLastCol As Long
> Dim iCol As Long
> Dim cCols As Long
> Dim i As Long, j As Long
> Dim fAll As Boolean
> Dim sTemp
>
> Application.ScreenUpdating = False
>
> Worksheets("HR DB").Range("A5:BJ" & Range("B1499").End(xlUp).Row).Copy
> Workbooks.Add
> ActiveWorkbook.Worksheets(1).Range("A1").PasteSpecial Paste:=xlPasteValues
>
> 'Delete Inactive Status
> ans = "Inactive"
> With Columns(12)
> Do
> Set c = .Find(ans, LookIn:=xlValues)
> If Not c Is Nothing Then
> c.EntireRow.Delete
> End If
> Loop While Not c Is Nothing
> End With
>
> Columns("A:A").Delete Shift:=xlToLeft
> Columns("C:C").Cut
> Columns("A:A").Insert Shift:=xlToRight
> Columns("C:K").Delete Shift:=xlToLeft
>
> iLastRowPO = Workbooks("Contractor Master4.xls").Worksheets("PO
> Table").Cells(Rows.Count, "D").End(xlUp).Row
> For i = 5 To iLastRowPO
> If Not IsEmpty(Workbooks("Contractor Master4.xls").Worksheets("PO
> Table").Cells(i, "D")) Then
> sTemp = sTemp & "|" & Workbooks("Contractor
> Master4.xls").Worksheets("PO Table").Cells(i, "D").Value
> End If
> Next i
>
> iLastRow = Cells(Rows.Count, "B").End(xlUp).Row
> For i = 1 To iLastRow
> fAll = Cells(i, "C").Value = "--All--"
> iLastCol = Cells(i, Columns.Count).End(xlToLeft).Column
> If fAll Then
> For j = 2 To iLastCol
> If Cells(i, j).Value <> "" Then
> Cells(i, 1).Value = Cells(i, 1).Value & "|" & Cells(i,
> j).Value
> End If
> Next j
> Cells(i, 1).Value = Cells(i, 1).Value & sTemp
> Else
> For j = 2 To iLastCol
> If Cells(i, j).Value <> "" Then
> Cells(i, 1).Value = Cells(i, 1).Value & "|" & Cells(i,
> j).Value
> End If
> Next j
> End If
> cCols = IIf(iLastCol > 1, iLastCol - 1, 1)
> Cells(i, 2).Resize(, cCols).ClearContents
> Next i
>
> Columns("A:A").Replace What:="|--All--", Replacement:="", LookAt:=xlPart, _
> SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
> ReplaceFormat:=False
> Columns("A:A").HorizontalAlignment = xlLeft
> Application.ScreenUpdating = True
> End Sub
>
>
>

[Alok]

Steph,

After
Workbooks.Add

add the line
ActiveWorkbook.Windows(1).visible=false

This should help some.

Alok Joshi
"Steph" wrote:

> Is there any way I can prevent the screen from updating/"blipping" when I
> run code? I thought application.screenupdating=false would do it. But my
> code below adds a new workbook, copies some data to it, manipulates it, etc.
> And as it is doing that, my screen "blips", and I have App.screenupdating
> set to false. Is there a way to maybe set focus to the main worksheet so
> the end user won't see and blips of the new workbook being added and
> manipulated?
>
> Sub Preparetxt()
>
> Dim ans As String
> Dim c As Range
> Dim iLastRow As Long
> Dim iLastRowPO As Long
> Dim iLastCol As Long
> Dim iCol As Long
> Dim cCols As Long
> Dim i As Long, j As Long
> Dim fAll As Boolean
> Dim sTemp
>
> Application.ScreenUpdating = False
>
> Worksheets("HR DB").Range("A5:BJ" & Range("B1499").End(xlUp).Row).Copy
> Workbooks.Add
> ActiveWorkbook.Worksheets(1).Range("A1").PasteSpecial Paste:=xlPasteValues
>
> 'Delete Inactive Status
> ans = "Inactive"
> With Columns(12)
> Do
> Set c = .Find(ans, LookIn:=xlValues)
> If Not c Is Nothing Then
> c.EntireRow.Delete
> End If
> Loop While Not c Is Nothing
> End With
>
> Columns("A:A").Delete Shift:=xlToLeft
> Columns("C:C").Cut
> Columns("A:A").Insert Shift:=xlToRight
> Columns("C:K").Delete Shift:=xlToLeft
>
> iLastRowPO = Workbooks("Contractor Master4.xls").Worksheets("PO
> Table").Cells(Rows.Count, "D").End(xlUp).Row
> For i = 5 To iLastRowPO
> If Not IsEmpty(Workbooks("Contractor Master4.xls").Worksheets("PO
> Table").Cells(i, "D")) Then
> sTemp = sTemp & "|" & Workbooks("Contractor
> Master4.xls").Worksheets("PO Table").Cells(i, "D").Value
> End If
> Next i
>
> iLastRow = Cells(Rows.Count, "B").End(xlUp).Row
> For i = 1 To iLastRow
> fAll = Cells(i, "C").Value = "--All--"
> iLastCol = Cells(i, Columns.Count).End(xlToLeft).Column
> If fAll Then
> For j = 2 To iLastCol
> If Cells(i, j).Value <> "" Then
> Cells(i, 1).Value = Cells(i, 1).Value & "|" & Cells(i,
> j).Value
> End If
> Next j
> Cells(i, 1).Value = Cells(i, 1).Value & sTemp
> Else
> For j = 2 To iLastCol
> If Cells(i, j).Value <> "" Then
> Cells(i, 1).Value = Cells(i, 1).Value & "|" & Cells(i,
> j).Value
> End If
> Next j
> End If
> cCols = IIf(iLastCol > 1, iLastCol - 1, 1)
> Cells(i, 2).Resize(, cCols).ClearContents
> Next i
>
> Columns("A:A").Replace What:="|--All--", Replacement:="", LookAt:=xlPart, _
> SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
> ReplaceFormat:=False
> Columns("A:A").HorizontalAlignment = xlLeft
> Application.ScreenUpdating = True
> End Sub
>
>
>