+ Reply to Thread
Results 1 to 8 of 8

Error next without for and how to restore all tables to only three rows.

  1. #1
    Forum Contributor
    Join Date
    11-15-2008
    Location
    Arizona
    MS-Off Ver
    Excel 2013
    Posts
    170

    Error next without for and how to restore all tables to only three rows.

    I am attempting to write vba code that will restore all the sheets except the "setup page" to its original state. After users setup their workbook, users will enter in data throughout the year and add rows to named tables as they need to enter a new entry. Then at the end of each year, they need to save it as that year, clear out the data, and restore all the tables to only three rows. This way it is then ready to add in data for the next year.

    I wrote the first part of the code that is meant to clear out all unlocked cells (essentially clear out everything the user entered in for that year but keep what they entered into their setup page).

    However, I get a next without for error. It highlights Nxt sheet and gives that error, but there is a for statement. For each sheet In Workbook . . . so maybe I nested incorrectly:

    Please Login or Register  to view this content.
    Then once I get that part of the code working, I need to add code that will restore all tables in all the worksheets (except the setup page) to three rows only (deleting all rows they added throughout the year). But I have no idea how to write that part of the code.

    All the tables are named. On the Average Hire Rate worksheet, there is one table named hirerate. On the Quarter 1-Quarter 4 worksheets, there are five tables on each worksheet. They are named Service 1, Service 2, Service 3, Service 4, and Service 5 on quarter 1 sheet. On quarter 2 sheet, they are named Service2.1, Service2.2, Service2.3, Service2.4, Service2.5. Then quarter 3 sheet, they are named Service3.1, Service3.2 etc.
    Last edited by dsrt16; 03-26-2018 at 07:12 PM.

  2. #2
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Error next without for and how to restore all tables to only three rows.

    Hi,

    You just need an End If line after the Next cell line.
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  3. #3
    Forum Contributor
    Join Date
    11-15-2008
    Location
    Arizona
    MS-Off Ver
    Excel 2013
    Posts
    170

    Re: Error next without for and how to restore all tables to only three rows.

    Ah duh. Thanks.

    But now I got this "Object doesn't support this property or method," and it highlighted this line:

    Please Login or Register  to view this content.

  4. #4
    Forum Contributor
    Join Date
    11-15-2008
    Location
    Arizona
    MS-Off Ver
    Excel 2013
    Posts
    170

    Re: Error next without for and how to restore all tables to only three rows.

    Oh wait. I forgot Sht.name. :D

  5. #5
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Error next without for and how to restore all tables to only three rows.

    You need sht.name

  6. #6
    Forum Contributor
    Join Date
    11-15-2008
    Location
    Arizona
    MS-Off Ver
    Excel 2013
    Posts
    170

    Re: Error next without for and how to restore all tables to only three rows.

    I ran the code, and it also cleared the unlocked cells on the Setup Page.

    So now the code runs with no debug erorrs, but it is clearing the Setup Page. I tried Sht.name <> "Setup Page" and Sht.name <> "Sheet7"

    Both times it cleared the Setup Page (sheet 7).

  7. #7
    Forum Contributor
    Join Date
    11-15-2008
    Location
    Arizona
    MS-Off Ver
    Excel 2013
    Posts
    170

    Re: Error next without for and how to restore all tables to only three rows.

    You solved my original question, so should I mark this as solved and then repost since this is a new problem? Or just keep the issue on this thread.

    New Issue: Setup Page is clearing even when code says to not run it on that page.

  8. #8
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Error next without for and how to restore all tables to only three rows.

    That means that the sheet name is not exactly Setup Page. Note that it is case sensitive and check for any trailing spaces.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Restore Cut Copy for Rows/Columns
    By jomili in forum Excel General
    Replies: 3
    Last Post: 06-22-2015, 04:06 PM
  2. [SOLVED] Insert Rows on Criteria and Copy content to Specific Cells in New Rows(Tables)
    By TheScott in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-01-2015, 03:38 PM
  3. Replies: 4
    Last Post: 04-24-2013, 04:58 AM
  4. Remove/restore rows of table based on conditional statments
    By NAK91 in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 12-13-2012, 03:14 PM
  5. [SOLVED] How do I restore header numbers for rows in a worksheet?
    By JLGWhiz in forum Excel General
    Replies: 2
    Last Post: 07-24-2006, 04:30 PM
  6. [SOLVED] Can i restore a document that i copied over in error?
    By Maryk in forum Excel General
    Replies: 4
    Last Post: 02-20-2006, 05:15 PM
  7. Pivot Tables: How do I show ALL field rows, including empty rows??
    By ekongirl@gmail.com in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-08-2005, 02:06 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1