macro works on excel2007 but not 2010

[lindatmw]

Hi,

Wonder if anyone has come across this problem and found a solution...

I have a macro that works fine on excel2007 but not on 2010. I keep getting the following msg:
clipboard can't be emptied, another prog might be using the clipboard and if I click to VB to continue, I get,
Run time error '1004' cannot shift objects off sheet
If I try to get out without saving, it will come up with:
The picture is too large and will be truncated
AND then excel stops working and shuts itself down.

I've tried clearing all clipboard at the top left corner and also tried ticking the "ALL" in the Advance option to display options for this worksheet. Nothing seems to work.

When I uninstall and reinstall MS Office 2010, it works for a couple of times and then I get the same messages again.

Any help would be greatly appreciated... I'm running out of ideas.

THANK you in advance.
linda

 

[Michael M]

Hi Linda
It might pay to post the code, but do you have any comments / shapes on the worksheet ?? Are you deleting or removing columns, so as to make any objects bigger than the remaining page ??
Also, have a look here, may help
http://support.microsoft.com/kb/211769

 

[lindatmw]

Thank you for prompt help Michael.

Here is my macro... I'm only a beginner in this and part of the macro was written by prof but added by me...it's been working with the additions and still works on excel2007.

I've copied the macro below... I tried your link but don't understand enough to try the shape instructions. HAve already tried Method 1 but don't know enough to do method 2.

I've also noted where the macro stops working and when the error msg actually comes on.

THANK YOU AGAIN IN ADVANCE!!!!
linda


Sub receipt()
'
' aasgenreceipt Macro
' Macro recorded 5/19/2006 by xxx
Sheets("receipts").Select
'deleting the void rows
Dim sCellID As String
Dim iRow As Integer
Dim LastRow As Long
Dim r As Long
LastRow = ActiveSheet.UsedRange.Rows.Count
LastRow = LastRow + ActiveSheet.UsedRange.Row - 1
Application.ScreenUpdating = False

For r = LastRow To 2 Step -1

If Range("C" & r) = "Void" Then
Rows(r).Delete

End If
Next r

'unhiding columns
Columns("A:BA").Select
Selection.EntireColumn.Hidden = False
'change name of col receipt #
Range("D1").Select
ActiveCell.FormulaR1C1 = "Reference"
Range("V1").Select
ActiveCell.FormulaR1C1 = "Receipt Number"

'establish the last row

Range("O1").Select
ActiveCell.End(xlDown).Select
iRow = ActiveCell.Row 'bottom cell of the total range b4 cut

'Fill down for receipts with more than 1 row
For Each C In Worksheets("RECEIPTS").Range("C2:K" + CStr(iRow)).Cells
If (C.Value) = "" Then C.FormulaR1C1 = "=(+R[-1]C)"
Next




'add the dates
Range("E2").Select
MyNum = Application.InputBox("Enter the current date:mm/dd/yy as text")
ActiveCell.FormulaR1C1 = MyNum
' Selection.NumberFormat = "m/d/yyyy;@"
Selection.NumberFormat = "@"
Range("E2").Copy
Range("E3:E" + CStr(iRow)).Select
ActiveSheet.Paste



'input deposit ticket ID

Cells(2, 1).Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[5],RC[4])"
ActiveCell.Copy
Range("A3:A" + CStr(iRow)).Select
ActiveSheet.Paste

'set the values for cells up to col K -Deposit Ticket ID
Range("A2:K" + CStr(iRow)).Copy
Range("A2:K" + CStr(iRow)).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

'Sort out the receipts with invoices
Range("A1:V1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort Key1:=Range("L2"), Order1:=xlDescending, Key2:=Range("C2") _
, Order2:=xlDescending, Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:= _
xlSortNormal

'change deposit ticket names
Columns("A:A").Select
Selection.Replace What:="zpal", Replacement:="z", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="Giro", Replacement:="g", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="Visa", Replacement:="v", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="Check", Replacement:="c", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="Amex", Replacement:="ax", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="MC", Replacement:="v", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="/", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False



'WORKSHEET WON"T INSERT COL STARTING FM HERE BUT WILL STILL RUN UNTIL FURTHER DOWN



'cut range with invoices
Cells(1, 12).Select
ActiveCell.End(xlDown).Select
Dim iInv As Integer
iInv = ActiveCell.Row
Range("A2:V" + CStr(iInv)).Cut
ActiveSheet.Paste Destination:=Worksheets("Receipt-inv").Range("A2")

'delete the top empty rows in the receipts sheet
'Worksheets("receipts").Select
Range(Cells(2, 1), Cells(iInv, 1)).Select
Selection.EntireRow.Delete Shift:=xlUp

'Set the new bottom row
Cells(1, 1).Select
ActiveCell.End(xlDown).Select
Dim iBot As Integer
iBot = ActiveCell.Row

'calc gst excl price and put to cell Unit price
Range("R2").Select
ActiveCell.FormulaR1C1 = "=Round(+RC[+1]/1.07,2)"
Range("R2").Copy
Range("R3:R" + CStr(iBot)).Select
ActiveSheet.Paste

'sort and sum by customer name
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.Worksheets("receipts").Sort.SortFields.Add Key:=Range( _
"C2:C194"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
Selection.Subtotal GroupBy:=3, Function:=xlSum, TotalList:=Array(18), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
'set the range to values after summing
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False


'reset the last row
Range("R1").Select
ActiveCell.End(xlDown).Select
rRow = ActiveCell.Row


'redo the dates
Range("E2").Select
ActiveCell.FormulaR1C1 = MyNum
Range("E2").Copy
Range("E3:E" + CStr(rRow)).Select
ActiveSheet.Paste



'Add a GST row to each receipt
For i = 3 To (iBot + iBot)
If Worksheets("receipts").Cells(i, 3).Value <> Cells(i - 1, 3) Then

Worksheets("receipts").Cells(i, 3).Select


'Selection.EntireRow.Insert
ActiveCell.FormulaR1C1 = "=(+R[-1]C)" 'Cust Name
ActiveCell.Offset(0, -2).FormulaR1C1 = "=(+R[-1]C)" 'Deposit Ticket ID
ActiveCell.Offset(0, 1).FormulaR1C1 = "=(+R[-1]C)" 'Reference
ActiveCell.Offset(0, 2).FormulaR1C1 = MyNum 'Date
ActiveCell.Offset(0, 3).FormulaR1C1 = "=(+R[-1]C)" 'payt type
ActiveCell.Offset(0, 5).FormulaR1C1 = "=(+R[-1]C)" 'Cash Amt
ActiveCell.Offset(0, 8).FormulaR1C1 = "=((+R[-1]C)-1)" '# of Dist
ActiveCell.Offset(0, 12).FormulaR1C1 = "GST" 'Item ID
'ActiveCell.Offset(0, 16).FormulaR1C1 = "=-((+R[-1]C[-11])-((+R[-1]C[-11])/1.07))" 'Item Description
ActiveCell.Offset(0, 16).FormulaR1C1 = "=-Round((+R[-1]C[-11])-RC[-1],2)" 'GST calc
ActiveCell.Offset(0, 17).FormulaR1C1 = "IRAS" 'tax authority
i = i + 1
Else
i = i

End If
Next i

'define the new range
Worksheets("Receipts").Select
Cells(2, 1).Select

Dim iRec As Integer
ActiveCell.End(xlDown).Select
iRec = ActiveCell.Row 'define bottom of the new receipts range
'


'set the values for GST calc
Columns("Q:T").Copy
Columns("Q:T").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

'insert col for sales tx code
Range("I1").Select


Selection.EntireColumn.Insert ' MACRO STOPS HERE WITH ERROR MESSAGE



Range("I1").FormulaR1C1 = "Sales Tax Code"

'Fill in all the rest of the data

For i = 2 To iRec
Sheets("receipts").Select
sCellID = "G" + CStr(i) 'Cash Acct
Range(sCellID) = "1002"
sCellID = "I" + CStr(i) 'Sales Tax Code
Range(sCellID) = "GST@7%"
sCellID = "J" + CStr(i) 'Prepayt
Range(sCellID) = "FALSE"
sCellID = "K" + CStr(i) 'Vendor Receipt
Range(sCellID) = "FALSE"
sCellID = "L" + CStr(i) 'No of transactions
sNo = Range(sCellID)
Range(sCellID).Value = sNo + 1
sCellID = "Q" + CStr(i) 'Description
Range(sCellID) = "=VLOOKUP(RC[-1],ITEMaas!R1C1:R100C8,2,0)"
sCellID = "R" + CStr(i) 'GL Acct
Range(sCellID) = "=VLOOKUP(RC[-2],ITEMaas!R1C1:R100C8,6,0)"
'sCellID = "U" + CStr(i) 'Transaction Period

'Range(sCellID) = MyNum

'to reverse sign of the Amt on rows that is not GST
If Worksheets("receipts").Cells(i, 16).FormulaR1C1 <> "GST" Then
sCellID = "T" + CStr(i) 'Amt
sAmtt = Range(sCellID)
Range(sCellID) = Round(-sAmtt / 1.07, 2)
End If
Next i

'round calc amts to 2 decimal pts
Columns("T:T").Select
Selection.NumberFormat = "0.00"
'Fill down for receipts ref with more than 1 row
For Each C In Worksheets("RECEIPTS").Range("W2:W" + CStr(rRow)).Cells
If (C.Value) = "" Then C.FormulaR1C1 = "=(+R[-1]C)"
Next

'delete the values in the unit price col
'Range("S2").Select
'Range(Selection, Selection.End(xlDown)).Select
'Selection.Clear

'delete the Transaction period col U and Unit price col S
Columns("V").Delete
Columns("S").Delete



End Sub

 

[Michael M]

I notice that I keep having a Stack Overflow error, every time I encounter this line.
Which suggests an out of memory type error. It's a bit beyond me, I'm afraid, but my reply will bump you back to the top and others might be able to get a better handle on the problem.

ActiveCell.End(xlDown).Select

AND
I also note that there are a number of variables not dimmed, like this one

rRow= ActiveCell.Row

 

[lindatmw]

Thank you very much anyways...

Hopefully somebody else can help.

Have a great week!
linda