+ Reply to Thread
Results 1 to 15 of 15

Loop Sheets excluding Sheet 1 Not moving to next sheet

  1. #1
    Forum Contributor
    Join Date
    10-24-2012
    Location
    Chicago
    MS-Off Ver
    Excel 2013
    Posts
    238

    Loop Sheets excluding Sheet 1 Not moving to next sheet

    Hello,

    I have the below code in a file. The file has a summary tab followed by a number of tabs that may change over time which come out of an accounting system in csv which isn't the cleanest format. The code does a reverse search for a number of different text items. Once it finds the one furthest up in the data i then selects the cell right above it and deletes all rows from that cell up. The macro works perfectly if I run it on a sheet by itself. As soon as I wrap loop code around it it debugs at the LastRow = .Range at the bottom of the code. I have change the code various times. Some versions of the code it will before but then debugs on ActiveCell.Offset where it performs the code on the one tab but then stays on that tab and performs it again causing it to bomb out because you can't offset -1 from cell A1. It should move to the next tab and run the code. It doesn't see to be doing that. Any help would be appreciated.

    Please Login or Register  to view this content.

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    18,403

    Re: Loop Sheets excluding Sheet 1 Not moving to next sheet

    All of your cell references are for the active sheet, so the first thing I would suggest doing is adding a With so they refer to the sheet(s) you are looping through.

    So something like this might be a start.
    Please Login or Register  to view this content.
    If posting code please use code tags, see here.

  3. #3
    Forum Contributor
    Join Date
    10-24-2012
    Location
    Chicago
    MS-Off Ver
    Excel 2013
    Posts
    238

    Re: Loop Sheets excluding Sheet 1 Not moving to next sheet

    Thanks for the reply. It is failing at ActiveCell. I have attached the file for you to take a look. It should loop through every tab except for the first tab and clean up the data so I am left with a shorted table. The code isn't done. eventually I will build a looking with the tab name and some other pieces of info to auto populate the first tab.
    Attached Files Attached Files

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    18,403

    Re: Loop Sheets excluding Sheet 1 Not moving to next sheet

    What is this part of the code meant to do?
    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    10-24-2012
    Location
    Chicago
    MS-Off Ver
    Excel 2013
    Posts
    238

    Re: Loop Sheets excluding Sheet 1 Not moving to next sheet

    it is meant to start searching from the bottom up and find the one furthest up from the bottom. so some tabs don't have Anchor which would be the last one up from the bottom. You will see a tab in that section has Office Apartment as the last one up. I built it like that to step through so it finds the one furthest up so I can then go one cell higher and delete all rows above it.

  6. #6
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    18,403

    Re: Loop Sheets excluding Sheet 1 Not moving to next sheet

    I'm confused, that code doesn't search from the bottom up - it searches column A for each term.

    Do you have a set of terms, in a certain order that you wan't to search for and as soon as one is found you want to move onto the next section of code?

  7. #7
    Forum Contributor
    Join Date
    10-24-2012
    Location
    Chicago
    MS-Off Ver
    Excel 2013
    Posts
    238

    Re: Loop Sheets excluding Sheet 1 Not moving to next sheet

    Sorry. All the way towards the bottom of each tab is something similar to a square footage table with buckets. I am looking from the bottom up so that it searches to find where the top of the table starts so that it can delete all rows above that table. The on error code is starting to look for the last one and then move up the chain with the last one finding Anchor which would always be at the top of the table if it existed. See below. That code is working if you run it on one of the property tabs on its own. Does that make more sense? Once it removes all of the rows above I then continue to clean up the data.

    Anchor
    Occupied SF : 100.00% 3 514,028
    Vacant SF : 0
    Total SF : 100.00% 3 514,028
    Outparcel
    Occupied SF : 66.60% 2 18,730
    Vacant SF : 11.10% 1 3,126
    365+ SF : 22.20% 1 6,247
    Total SF : 100.00% 4 28,103
    Inline
    Occupied SF : 87.50% 48 344,358
    Vacant SF : 9.50% 7 37,404
    365+ SF : 2.90% 3 11,584
    Total SF : 100.00% 58 393,346
    Storage
    Vacant SF : 100.00% 28 6,438
    Total SF : 100.00% 28 6,438
    Food Court
    Occupied SF : 88.60% 7 5,871
    Vacant SF : 0
    365+ SF : 11.40% 1 755
    Total SF : 100.00% 8 6,626
    Non-GLA
    Occupied SF : 53.50% 6 1,150
    Vacant SF : 46.50% 2 1,000
    Total SF : 100.00% 8 2,150
    Total Property : 4565 Oakwood Center LA
    Occupied SF : 95.00% 71 902,723
    Vacant SF : 5.00% 38 47,968
    Total SF : 100.00% 109 950,691
    Total Company 4565 Oakwood Center LA
    Occupied SF : 95.00% 71 902,723
    Vacant SF : 5.00% 38 47,968
    Total SF : 100.00% 109 950,691

  8. #8
    Forum Contributor
    Join Date
    10-24-2012
    Location
    Chicago
    MS-Off Ver
    Excel 2013
    Posts
    238

    Re: Loop Sheets excluding Sheet 1 Not moving to next sheet

    However not every tab may end with Anchor so it needs to be able to for example stop at Office Apartment on one of the tabs because that is the start of the table.

  9. #9
    Forum Contributor
    Join Date
    10-24-2012
    Location
    Chicago
    MS-Off Ver
    Excel 2013
    Posts
    238

    Re: Loop Sheets excluding Sheet 1 Not moving to next sheet

    does my response make sense now?

  10. #10
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    18,403

    Re: Loop Sheets excluding Sheet 1 Not moving to next sheet

    Makes a bit more sense.

    I'll need to take a closer look at the workbook though which I can't do right now.

    PS Does the code I posted in post #2 work at all?

  11. #11
    Forum Contributor
    Join Date
    10-24-2012
    Location
    Chicago
    MS-Off Ver
    Excel 2013
    Posts
    238

    Re: Loop Sheets excluding Sheet 1 Not moving to next sheet

    Hi, Thanks again. The only code I see is the first one you posted which failed at ActiveCell and then the second code which was just a copy of my search code with On Error where you were questioning what it does. It searches column a in reverse bottom up and it is coded in a way where it is in a hierarchy in reverse order so that it will step up column A until it finds the top of the structure of the table which isn't always Anchor which is why I had to code it that way. Then it deletes all lines above that table.

  12. #12
    Forum Contributor
    Join Date
    10-24-2012
    Location
    Chicago
    MS-Off Ver
    Excel 2013
    Posts
    238

    Re: Loop Sheets excluding Sheet 1 Not moving to next sheet

    so in the code there is

    For Each sheet In ActiveWorkbook.Worksheets
    If sheet.Index > 1 Then
    With sheet

    However it isn't skipping the first sheet. and it isn't moving to the next sheet. So the code alters the Consolidated Data tab removing everything because it didn't find the search criteria in column A which is right it doesn't have that criteria. Then it stays on the sheet and bombs out on the second loop because it tries to do the same thing on the tab it just altered and offset -1 is impossible because it is at cell A1 at that point. I am not sure why it isn't skipping over Sheet1 and not sure why it isn't move from tab to tab after sheet one.

  13. #13
    Forum Contributor
    Join Date
    10-24-2012
    Location
    Chicago
    MS-Off Ver
    Excel 2013
    Posts
    238

    Re: Loop Sheets excluding Sheet 1 Not moving to next sheet

    Hi Norie,

    Just following up to see if you had any ideas?

  14. #14
    Forum Contributor
    Join Date
    10-24-2012
    Location
    Chicago
    MS-Off Ver
    Excel 2013
    Posts
    238

    Re: Loop Sheets excluding Sheet 1 Not moving to next sheet

    Thanks for the help. someone by the handle of wideboydixon on MrExcel's site suggested the below which got the code working.

    Some of your code is working on the current sheet. For example, ActiveCell is the active cell on the current sheet. Without refactoring everything else, you could put this before the first "On Error" line:

    ws.Activate

  15. #15
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    18,403

    Re: Loop Sheets excluding Sheet 1 Not moving to next sheet

    Glad you got it working.

    Sorry for not getting back to you - been kind of busy the past few days.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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