Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Error on inserting rows (2000)

    The following routine is run on 4 worksheets one after the other.

    Private Sub InsertRowsAndMissingStaffDetails()

    j = 1
    While MissingStaff(j).StaffNo <> 0
    Range("A6").Activate
    While Val(ActiveCell.Value) < MissingStaff(j).StaffNo
    ActiveCell.Offset(1, 0).Activate
    Wend
    ActiveCell.EntireRow.Insert
    ActiveCell.Value = MissingStaff(j).StaffNo
    ActiveCell.Offset(0, 1).Value = MissingStaff(j).StaffName
    j = j + 1
    Wend
    End Sub

    The routine fails on the third worksheet and I get the following error message:

    Run time error 1004.
    To prevent loss of data excel cannot shift non-blank cells off the worksheet.
    Try deleting/clearing etc.....

    I have done all the relevant deleting and clearing etc.. but this still has not fixed the problem.
    Can someone please advise as to what to try next. In the past my only solution at this stage has been to insert a new sheet, copy over the required bits form the old sheet, delete the latter, and then rename the new sheet to the same as the old sheet. However in this instance i really need to copy over the whole of the old sheet.

  2. #2
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Error on inserting rows (2000)

    Is there any chance of your posting a full (suitably censored) copy of just the 3rd worksheet?
    Gre

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Error on inserting rows (2000)

    I thinnk this error occurs when you try to insert a row into a full worksheet (row 65536) is USED. Is there formatting in the last row or something else to give excel the impression that the last row is used?

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •