Solved

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

Posted on 2011-09-08
3
239 Views
Last Modified: 2012-05-12
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
Comment
Question by:UplandSystems
3 Comments
 
LVL 7

Accepted Solution

by:
m4trix earned 500 total points
ID: 36501772
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
 
LVL 4

Expert Comment

by:javaftper
ID: 36501792
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
 

Author Closing Comment

by:UplandSystems
ID: 36573895
Fixed the problem, the only issue being some formatting problems
0

Featured Post

Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Excel Formula to split product code 5 15
conditional formatting 4 42
Copy a range from 1..n excel sheets to one destination sheet 2 31
VLOOKUP 6 17
A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question