• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 255
  • Last Modified:

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
0
UplandSystems
Asked:
UplandSystems
1 Solution
 
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now