This code just copies values from the active ws to another wb and then
pastes values from the wb back to the active ws. It works fine but I
want to hide the wb and stop the screen updating until the code is
complete... How can I do this?


Sub Populate_H6()
Dim ws As Worksheet
Dim wb As Workbook
Dim rng As Range
Dim rCell As Range

Application.ScreenUpdating = False

Set ws = ActiveSheet
Set wb = Workbooks.Open("A:\Lookup.xls")

With ws
Set rng = .Range(.Cells(1, 1), _
.Cells(.Rows.Count, 2).End(xlUp))
End With

For Each rCell In rng.Cells
Select Case rCell.Value
Case "TiM"
rCell.Offset(0, 1).Copy wb.Worksheets(13) _
.Range(rCell.Address).Offset(3, 1)
rCell.Offset(0, 2).Copy wb.Worksheets(13) _
.Range(rCell.Address).Offset(3, 2)
wb.Worksheets(13).Range(rCell.Address) _
.Offset(3, 5).Copy ws.Range(rCell.Address).Offset(0, 3)
Case "MiM"
rCell.Offset(0, 1).Copy wb.Worksheets(13) _
.Range(rCell.Address).Offset(3, 7)
rCell.Offset(0, 2).Copy wb.Worksheets(13) _
.Range(rCell.Address).Offset(3, 8)
wb.Worksheets(13).Range(rCell.Address) _
.Offset(3, 11).Copy ws.Range(rCell.Address).Offset(0, 3)
Case "WiM"
rCell.Offset(0, 1).Copy wb.Worksheets(13) _
.Range(rCell.Address).Offset(3, 13)
rCell.Offset(0, 2).Copy wb.Worksheets(13) _
.Range(rCell.Address).Offset(3, 14)
wb.Worksheets(13).Range(rCell.Address) _
.Offset(3, 17).Copy ws.Range(rCell.Address).Offset(0, 3)
End Select
Next

With ws
ws.Columns("D").ClearFormats
ws.Columns("D").AutoFit
End With

wb.Close savechanges:=False

Application.ScreenUpdating = True

End Sub


Thank You,
-- Dan

It looks like you have the .screenupdating lines in the appropriate spots.

Any chance that you have some event (workbook_Open in lookup.xls???) that turns
the screenupdating back on?

If yes, you could turn off screenupdating right after you open lookup.xls--or
you could disable events before you open that file:

application.enableevents = false
set wb = workbooks.open(....
application.enableevents = true

You could have events that fire when you're making a selection change or a
worksheet change, too. You may want to spend some time isolating where the
problem occurs.

By the way, I wouldn't open a file that's on my floppy. I'd copy it to my
harddrive and open it from there.

"Dan R." wrote:
>
> This code just copies values from the active ws to another wb and then
> pastes values from the wb back to the active ws. It works fine but I
> want to hide the wb and stop the screen updating until the code is
> complete... How can I do this?
>
> Sub Populate_H6()
> Dim ws As Worksheet
> Dim wb As Workbook
> Dim rng As Range
> Dim rCell As Range
>
> Application.ScreenUpdating = False
>
> Set ws = ActiveSheet
> Set wb = Workbooks.Open("A:\Lookup.xls")
>
> With ws
> Set rng = .Range(.Cells(1, 1), _
> .Cells(.Rows.Count, 2).End(xlUp))
> End With
>
> For Each rCell In rng.Cells
> Select Case rCell.Value
> Case "TiM"
> rCell.Offset(0, 1).Copy wb.Worksheets(13) _
> .Range(rCell.Address).Offset(3, 1)
> rCell.Offset(0, 2).Copy wb.Worksheets(13) _
> .Range(rCell.Address).Offset(3, 2)
> wb.Worksheets(13).Range(rCell.Address) _
> .Offset(3, 5).Copy ws.Range(rCell.Address).Offset(0, 3)
> Case "MiM"
> rCell.Offset(0, 1).Copy wb.Worksheets(13) _
> .Range(rCell.Address).Offset(3, 7)
> rCell.Offset(0, 2).Copy wb.Worksheets(13) _
> .Range(rCell.Address).Offset(3, 8)
> wb.Worksheets(13).Range(rCell.Address) _
> .Offset(3, 11).Copy ws.Range(rCell.Address).Offset(0, 3)
> Case "WiM"
> rCell.Offset(0, 1).Copy wb.Worksheets(13) _
> .Range(rCell.Address).Offset(3, 13)
> rCell.Offset(0, 2).Copy wb.Worksheets(13) _
> .Range(rCell.Address).Offset(3, 14)
> wb.Worksheets(13).Range(rCell.Address) _
> .Offset(3, 17).Copy ws.Range(rCell.Address).Offset(0, 3)
> End Select
> Next
>
> With ws
> ws.Columns("D").ClearFormats
> ws.Columns("D").AutoFit
> End With
>
> wb.Close savechanges:=False
>
> Application.ScreenUpdating = True
>
> End Sub
>
> Thank You,
> -- Dan

--

Dave Peterson

Oh I know, I mapped a network drive on A: for some odd reason. But yes
you're right I do have a worksheet_change event on the Lookup sheet.
This is probably a stupid question but if I turn off screenupdating
after I open Lookup then it's still going to show it open it correct?
Or do I have to turn it off both before and after I open Lookup?

Thanks Dave,
-- Dan

nevermind, you're exactly right... that worked. I hate to push my luck
but could you please give me some insight as to how to display as
progress bar while the code is running?

Thanks,
-- Dan

That first section of code is pretty small and doesn't do any screenupdating
anyway. You're not gaining too much by turning off screenupdating before the
..open line.

And if you turn off screenupdating after you open lookup.xls, then it'll show
up. But you could activate the other workbook right after you open the file,
then turn off screenupdating--or turn off the events.

Set ws = ActiveSheet
Set wb = Workbooks.Open("A:\Lookup.xls")

ws.parent.activate
Application.ScreenUpdating = False

With ws
Set rng = .Range(.Cells(1, 1), _
.Cells(.Rows.Count, 2).End(xlUp))
End With

=====
might be ok.


"Dan R." wrote:
>
> Oh I know, I mapped a network drive on A: for some odd reason. But yes
> you're right I do have a worksheet_change event on the Lookup sheet.
> This is probably a stupid question but if I turn off screenupdating
> after I open Lookup then it's still going to show it open it correct?
> Or do I have to turn it off both before and after I open Lookup?
>
> Thanks Dave,
> -- Dan

--

Dave Peterson

I'd use the statusbar at the bottom.

Application.statusbar = "Processing: " & mycell.row & " at: " & now
Inside the loop

then at the end, give the statusbar back to excel:

application.statusbar = false

=====
But if you want a statusbar, you can look at John Walkenbach's site:
http://www.j-walk.com/ss/excel/tips/tip34.htm



"Dan R." wrote:
>
> nevermind, you're exactly right... that worked. I hate to push my luck
> but could you please give me some insight as to how to display as
> progress bar while the code is running?
>
> Thanks,
> -- Dan

--

Dave Peterson

Works great Dave.

Thank You,
-- Dan