Code hanging on one line every other time it's run??

[Don....confused again??]

Hi All,

Below is the code...this thing hangs every other time I
run it. And hangs on the same line. Can anyone tell me
what's making it do that, please? I know I'm using a lot
of Dim statements but just trying to practice with them.

I've shown the line where it hangs with *** on both sides
of it:

Dim rRge3 As Range
Dim rRge4 As Range
Dim rRge5 As Range
Dim rRge6 As Range
Dim rRge7 As Range
Dim rRge8 As Range
Dim rRge9 As Range

Set rRge3 = Sheets("Data").Range("A4:FF500")
Set rRge4 = Sheets("Data").Range("A4")
Set rRge5 = Sheets("Upgrade Invoice").Range("A1:K49")
Set rRge6 = Sheets("Upgrade Invoice (2)").Range
("A1:K49")
Set rRge7 = Sheets("Upgrade Invoice (2)").Range
("B7:K46")
Set rRge8 = Sheets("Upgrade Invoice (2)").Range("B7")
Set rRge9 = Sheets("Upgrade Invoice (2)").Range("A1")

With Sheets("Data")
.Select
.Unprotect
rRge3.Select
Selection.Sort Key1:=Range("BA4"),
Order1:=xlDescending, HEADER:=xlGuess, _
OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom
rRge4.Select
.Protect
End With
With Sheets("Upgrade Invoice")
.Select
.Unprotect
rRge5.Select
Selection.Copy
End With
With Sheets("Upgrade Invoice (2)")
.Select
.Unprotect
rRge9.Select
*** .Paste ****
rRge6.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
rRge7.Select
Selection.Sort Key1:=Range("H7"),
Order1:=xlAscending, HEADER:=xlGuess, _
OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom
rRge8.Select
.Protect
.Copy
End With
Range("B7").Select
ActiveSheet.Protect DrawingObjects:=True,
Contents:=True, Scenarios:=True
' ActiveWorkbook.Protect Structure:=True, Windows:=False
ActiveWorkbook.SaveAs
Filename:="C:\WirelessLedger\Upgrade Invoices\" _
& Format(Date, "mm-dd-yy") & " Upgrade Inv" & ".xls", _
FileFormat:=xlNormal, Password:="",
WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWindow.SelectedSheets.PrintOut Copies:=1,
Collate:=True
Range("H1").Select
Selection.Hyperlinks(1).Follow NewWindow:=False,
AddHistory:=True
Range("B7").Select
ActiveSheet.Protect
ActiveWindow.Close
With Sheets("Upgrade Invoice (2)")
.Unprotect
rRge6.Select
Selection.Clear
rRge9.Select
.Protect
End With

Any help here would be greatly appreciated,

TIA,

Don

 

[hgrove]

Don....confused again?? wrote...

Below is the code...this thing hangs every other time I run it. An hangs on the
same line. Can anyone tell me what's making it do that, please? know I'm
using a lot of Dim statements but just trying to practice with them. ...
With Sheets("Upgrade Invoice")
.Select
.Unprotect
rRge5.Select
Selection.Copy
End With

Are you missing a .Protect call before the End With, or have you lef
the 'Upgrade Invoice' worksheet unprotected on purpose? If all you'r
doing is copying rRge5 to the Clipboard, why are you unprotecting th
worksheet?

With Sheets("Upgrade Invoice (2)")
.Select
.Unprotect
rRge9.Select
*** .Paste ****

...

It's a mystery why this .Paste call would ever work because th
.Unprotect call should eliminate whatever you had in the Clipboard. Fo
the section of your macro above, you should use the following instead.

Sheets("Upgrade Invoice (2)").Unprotect
rRge5.Copy Destination:=rRge9
Sheets("Upgrade Invoice (2)").Protect

Your code has way to many .Select calls. These are almost alway
unnecessary, and always slow down macros

 

[Don]

Thanks for the reply and the critic Harlan,

As you can very easily tell, I'm trying to learn here and
all help and constructive comments are very much
appreciated.

I'll take a look through that code again and include your
suggestions. I'll be back and let you know how I came out.

I still don't know why it would work one time and not the
next though.....ahhhh...the mysteries of Excel....

Thanks again for your time and knowledge here,

Don

 

[Tushar Mehta]

I haven't gone through all the code but maybe each time through you are
toggling the protected state. That would mean that some operation that
requires an unprotected workbook/sheet will work every alternate time.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Don]

Hi Tushar,

Thanks and I do appreciate taking the time to look at
this. I've looked at it till I'm cross-eyed and I can't
find it, however that doesn't surprise me, I'm just
learning these macros. (and slowly, I might add) Anyway, I
took some of Harlans suggestions and rewrote the entire
macro and it seems to be working ok now. I've got a copy
of the old code and will be looking at it again this next
week when I've got a bit more time. I really would like to
know what happened so as to avoid it next time.

With respect to the protect codes, immediately before the
line that hangs there is an .unprotect line, and as far as
I tell, using the VBA editor and stepping through the
code, it steps right by that line leaving the
sheet "Unprotected" at that time. Whatever the error is,
it's very consistent...fails every other run....but bottom
line is, I do have the WB working correctly now......phewww

Thanks again for taking a look and responding here.

Hope you have a great day,

Don

 

[Tushar Mehta]

You are welcome and glad you got that straightened out. Thanks for
letting folks know.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

Hi Tushar,

Thanks and I do appreciate taking the time to look at
this. I've looked at it till I'm cross-eyed and I can't
find it, however that doesn't surprise me, I'm just
learning these macros. (and slowly, I might add) Anyway, I
took some of Harlans suggestions and rewrote the entire
macro and it seems to be working ok now. I've got a copy

{snip}