+ Reply to Thread
Results 1 to 10 of 10

Check same cell on range of worksheets and if not blank, copy into a list on another sheet

  1. #1
    Registered User
    Join Date
    06-26-2015
    Location
    Tampa
    MS-Off Ver
    2010
    Posts
    17

    Question Check same cell on range of worksheets and if not blank, copy into a list on another sheet

    The workbook contains 202 sheets. I want to search cell G19 on 196 of them. For every instance where G19 is not blank (that is, it contains text and/or numbers--I don't care) copy the contents of that G19 to a list on a separate sheet.

    For example:

    I want to
    Ignore Sheet "PS5"
    Ignore Sheet "PS10"
    Sheet "PS15" G19 contains "No handball courts"
    Sheet "PS20" G19 is blank
    Sheet "PS25" G19 is blank
    Sheet "PS30" G19 contains "Too many handball courts"
    Ignore Sheet "PS35"
    etc...

    And then
    My summary sheet "FirstWorldProblems" should list "No handball courts," and the cell right below this should contain "Too many handball courts" with no blank rows between.

    I found some code to list the contents of every worksheet cell that matches the active cell when I run a macro. I don't understand a word of it.

    I also found some code to search through a column of, yes, 196 rows and copy its contents to another column on the same worksheet if the adjacent cell is not blank.

    These are close, but I can't figure how to modify either to make this work. (Had to delete the code to get this through the firewall.)

    Your expertise and patience are much appreciated. I will not be offended if you laugh at me.

    Many thanks to all.

  2. #2
    Valued Forum Contributor bulina2k's Avatar
    Join Date
    11-20-2012
    Location
    Urziceni, Ialomita, Romania
    MS-Off Ver
    2019 and 365
    Posts
    861

    Re: Check same cell on range of worksheets and if not blank, copy into a list on another s

    Hi there Edddie,
    Do you think you can upload a dummy excel file with the a before sheet and an after sheet (wanted results) Maybe even a couple of those sheets you're talking about ?
    .. and don't forget to have fun!
    Bogdan.

    mark SOLVED and Add Reputation if my answer pleases you

  3. #3
    Registered User
    Join Date
    06-26-2015
    Location
    Tampa
    MS-Off Ver
    2010
    Posts
    17

    Re: Check same cell on range of worksheets and if not blank, copy into a list on another s

    I hope this is enough to illustrate what I'm trying to do. It took some doing to get it under the 1MB limit. Thanks for being willing to have a look.
    Attached Files Attached Files
    Last edited by eddiecruz; 12-14-2015 at 03:12 PM.

  4. #4
    Valued Forum Contributor bulina2k's Avatar
    Join Date
    11-20-2012
    Location
    Urziceni, Ialomita, Romania
    MS-Off Ver
    2019 and 365
    Posts
    861

    Re: Check same cell on range of worksheets and if not blank, copy into a list on another s

    I'm sorry but I fail to understand what is to be done here. Which one is the results worksheet? What data should go where?

  5. #5
    Registered User
    Join Date
    06-26-2015
    Location
    Tampa
    MS-Off Ver
    2010
    Posts
    17

    Re: Check same cell on range of worksheets and if not blank, copy into a list on another s

    No, I'M sorry for doing such a poor job of explaining myself.

    Col E of the "Before" sheet is the one where I want to compile a list of VerifcationsNeeded. I want to check cell G19 of the range of worksheets NOT named Skip me, and if that cell is not blank, list what verifications need to be made. If G19 IS blank, do nothing. So the "After" sheet shows what I would end up with.

    Apologies for putting the dumb in newbie.
    Last edited by eddiecruz; 12-14-2015 at 03:28 PM.

  6. #6
    Valued Forum Contributor bulina2k's Avatar
    Join Date
    11-20-2012
    Location
    Urziceni, Ialomita, Romania
    MS-Off Ver
    2019 and 365
    Posts
    861

    Re: Check same cell on range of worksheets and if not blank, copy into a list on another s

    Ok, but... there is no Before and After sheets in your uploaded file..

  7. #7
    Registered User
    Join Date
    06-26-2015
    Location
    Tampa
    MS-Off Ver
    2010
    Posts
    17

    Re: Check same cell on range of worksheets and if not blank, copy into a list on another s

    That's very odd. When I download it, I see them. They're even colored red. I'll try again. Sorry for the hassle.
    Attached Files Attached Files

  8. #8
    Valued Forum Contributor bulina2k's Avatar
    Join Date
    11-20-2012
    Location
    Urziceni, Ialomita, Romania
    MS-Off Ver
    2019 and 365
    Posts
    861

    Re: Check same cell on range of worksheets and if not blank, copy into a list on another s

    btw, the first uploaded file had a Verification tab. I used that. OK the code is:
    Please Login or Register  to view this content.
    See the attached file also (right click on Verification sheet and View Code).
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    06-26-2015
    Location
    Tampa
    MS-Off Ver
    2010
    Posts
    17

    Re: Check same cell on range of worksheets and if not blank, copy into a list on another s

    Miraculous. I don't know what else to say. I think if I can deconstruct and understand your code, I'll be a lot farther down the road toward being able to do this sort of thing myself. Thanks so much for your kind help.

  10. #10
    Valued Forum Contributor bulina2k's Avatar
    Join Date
    11-20-2012
    Location
    Urziceni, Ialomita, Romania
    MS-Off Ver
    2019 and 365
    Posts
    861

    Re: Check same cell on range of worksheets and if not blank, copy into a list on another s

    Please mark thread as SOLVED (thread tools menu) if you consider. Thank you for the rep point.

+ 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. Check cell value in range, depending on condition copy to another Sheet
    By KemalO in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-28-2015, 11:01 AM
  2. [SOLVED] Copy / Paste range from one sheet to another, if cell in column A is not blank
    By Tapyr in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-01-2014, 09:18 AM
  3. VBA: Copy non-blank and paste to separate sheet ONLY blank cells (running list)
    By brolsen in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-10-2014, 03:34 PM
  4. [SOLVED] Check row by row and count blank cell and copy header numeric field in new sheet.
    By ExcelUser2707 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-15-2014, 02:38 AM
  5. macro to copy changing range to next blank cell in another sheet
    By d2whales in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-09-2013, 12:55 PM
  6. Check cell Value in range and copy specific cells to another sheet
    By jmethejedi in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-08-2013, 06:25 PM
  7. Macro to check for blank cell entry, copy previous value, and check for duplicates
    By xPunxNotDeadx in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-26-2009, 06:33 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