Macro-Run-time error '6'

[Dave Y]

I have an Excel wookbook that has three tabs. Two of the
tabs are for doing manual input such as adding and
deleting rows, or adding/deleting data. The third tab
(called Print) is meant to be read only where no manual
input is to be done. I have a macro that is run from a
button on the Print tab that combines all the manual input
from the other two tabs and formats it all nicely on the
Print tab for the purpose of updating the data and
printing it out. This macro works great. The problem I
have is that the macro formuls are located on the Print
tab and occasionally people who work in this workbook
forget not to do anything on the Print tab and they
sometimes add or delete rows and throw off the formulas.
So I created another tab called lookups and cut & pasted
the macro formulas to that tab with the intent of hiding
the lookups worksheet and eliminate the user errors. My
problem is that after I moved the formulas from the Print
worksheet to the lookups worksheet I am receiving a Run-
time error '6': Overflow when I run the macro. I have
included the code from the VB editor and I put *** before
the line of code that is highlighted when I run the
Debugger. I know this is a long post and I appreciate the
patience in reading it. Any help in resolving this macro
issue will be greatly appreciated.

Thanks, Dave (code below)

Dim cellloc As Integer
Dim cellloc2 As Integer
Application.Goto Reference:="clear"
Selection.RemoveSubtotal
Range("clear").Select
Selection.ClearContents
Application.Goto Reference:="formats"
Selection.Copy
Range("clear").Select
Selection.PasteSpecial Paste:=xlPasteFormats,
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("LTOB").Select
Range("A2:B2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Print").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.End(xlDown).Select
cellloc = ActiveCell.Row
Range("A" & cellloc + 1).Select
Sheets("LtrOC").Select
Range("A1").Select
Range("A2:B2").Activate
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Print").Select
ActiveSheet.Paste
Application.Goto Reference:="lookups"
Selection.Copy
Range("A2").Select
Selection.End(xlDown).Select
*** cellloc2 = ActiveCell.Row
Range("C2:C" & cellloc2).Select
ActiveSheet.Paste
Range("C2").Select
Range("A1:R" & cellloc2).Sort Key1:=Range("A2"),
Order1:=xlAscending, Key2:=Range _
("B2"), Order2:=xlAscending, Header:=xlGuess,
OrderCustom:=1, MatchCase _
:=False, Orientation:=xlTopToBottom,
DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal
Selection.Subtotal GroupBy:=1, Function:=xlSum,
TotalList:=Array(7, 8, 9, 10 _
), Replace:=True, PageBreaks:=False,
SummaryBelowData:=True
End Sub

 

[steve]

Dave,

For starters your code looks like it belongs in a standard module and not
the worksheet module (unless it is an event macro).

Second - you have a lot of "select" which can be eliminated.
Some examples:
Range("clear").Select
Selection.ClearContents
can be replaced with
Range("clear").ClearContents

Application.Goto Reference:="formats"
Selection.Copy
Range("clear").Select
Selection.PasteSpecial Paste:=xlPasteFormats,
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
can be replaced with
Range("formats").Copy
Range("clear").PasteSpecial Paste:=xlPasteFormats

and this works well to find the last row in column A
cellloc = Cells(Rows.COUNT, "A").End(xlUp).Row
or
cellloc = Range("A2").End(xlDown).Row

I am also concerned with using the terms "clear" & "formats". They are like
reserved words in Excel (place your cursor over one of these and hit the F1
key. If you get a help file than you should change it to something else.
Excel gets confused easily.

You can also hide the "Print" tab (another reserved word) and have a print
macro unhide it for printing and than rehide it with xlVeryHidden.

 

[Tom Ogilvy]

Dim cellloc As Integer
Dim cellloc2 As Integer

can only hold a value up to 32767

but rows can be up to 65536 - so

Dim cellloc as Long
Dim celloc2 as Long

would be the first thing I would try.

 

[Dave Y]

Hi Tom,

Thanks for your reply. I tried changing the variable type
from Integer to Long, but still receive the same Run-time
error. I don't understand why the macro works until I move
the formulas to a different worksheet. But thanks for the
input. Any other suggestions will be appreciated.

 

[Dave Y]

Hi Steve,

I edited my code using some of your suggestions and
everything seems to be working fine. I've been able to
move the formulas and hide the worksheet and run the macro
without receiveing the Run-time error message. I greatly
appreciate your help and your experience. Thank you very
much.

Dave

 

[Tom Ogilvy]

If you have the code in a worksheet module, then there are a lot of reasons
it won't work. every unqualified

Range("whatever").Select
will refer to the sheet containing the code - not the activesheet, even
though that is what you intended.

So when you do

Range("A2").Select
Selection.End(xldown)
cellloc2 = ActivceCell.Row

the active cell is probably A65536 which causes an overflow error when
cellloc2 is dimensioned as Integer.

If you declare it as long, you won't get the overflow error on that line.

You may get other errors or have an overflow error for some other variable
declared as Integer in another location.

Move you code into a general module. If you need to execute it from a
commandbutton or something, then just call it - but leave it in a general
module or learn real fast how to reference cells without use select and
activate.

 

[Dave Y]

Hi Tom,

You hit the nail right on the head. When I was receiveng
the error the code would bring me straight to cell
A65536. I ended up changing the code which I guess
changed how my data ranges were being referred to. I
really appreciate the replies I have received from this
newsgroup. You guys are great! It inspires me to keep
trying to learn and get better. Thank you very much.