Cannot create new row or paste data into new sheet, VB Error and Large action error

I have and excel sheet that i use for my accounts and payment from my clients, as part of this sheet i have a part payment button that supposed to deduct a given amount from a clients total bill, crerate a new row below with the new amount minus the part payment, however i get an error syaing the operastion is large. I have tryed this on a i7 machine with 8GB of RAM, so it is not a resource issue. I have attached screenshots of the errors.

The error appear when i click the part pay button which runs VB code, it has worked untill now and i am wondering why it has broke all of a sudden, i cannot even manually create a new row.

IO have trieed copying the data into a new sheet and the errors persist.

 error 1
 error 1
 error 1

error-2.PNG
error-3.PNG
Invoice-log.xlsm
UplandSystemsAsked:
Who is Participating?
 
m4trixCommented:
you have a ton of extra cells to the right and below your main data table. They look blank, but they're still being saved.  Clear them completely (Select Cols T -> XFD, click Clear->All, do the same for all the rows below your data), save the document, and your problem seems to go away

See attached, and note the length of both the vertical and horizontal scroll bars, compared to your version:
 Invoice-log-fixed.xlsm
0
 
javaftperCommented:
agreed.
m4trix is right.
You may also wish to consider adding error trapping to your code as below. In this example the error description relates to available resources being exceeded. Resources are not necessarily hardware related- in this example it relates to the resources available within the objects being updated.

On Error GoTo Errcatch


    ActiveCell.Offset(1, -8).Range("A1").Select
    Selection.EntireRow.Insert
    ActiveCell.Offset(-1, 0).Range("A1:C1").Select
    Selection.Copy
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveCell.Offset(0, 6).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=R[-1]C-R[-1]C[2]"
    ActiveCell.Offset(-1, 10).Range("A1").Select
    ActiveCell.FormulaR1C1 = "Part paid - see balance below"
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = "Balance outstanding"
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveCell.Offset(-2, -3).Range("A1").Select
    Selection.Copy
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
   
    'and at the end of the code put

Exit Sub

Errcatch:
MsgBox Err.Description
End Sub
0
 
UplandSystemsAuthor Commented:
Fixed the problem, the only issue being some formatting problems
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.