List Same Cell From Multiple Worksheets

  • Hi all,


    I was wondering if someone would be able to help modify the code that AAE posted for whitegoldf150's problem (http://www.ozgrid.com/forum/showthread.php?t=87516) - I'm trying to do the same thing, except I have blank cells that are being skipped when I run the script. I'd like to return even the blank cells so that every column is filled to the same last cell (as many as there are pages). (I've already replicated it to return other values in other columns so the spacing is off). Also is there is a way to fill values for a specific set of sheets (lets say all sheets before the summary sheet) instead of inserting excluded sheet names after the inequality "If ws.Name <> "Sheet1""?






    Thanks!


    GC052

  • Re: List Same Cell From Multiple Worksheets


    We would probably have to change this line.

    Code
    ActiveSheet.Paste Range("A65536").End(xlUp).Offset(1, 0)


    So the question is where do you want the first value to be pasted?


    The code can be written to work from wherever you say we can start and will allow blank cells.


    To answer your other question all sheets have an index number.


    The index number is determined by the order of the tabs, so if you put the tabs in a certain order and let's say you have 8 sheets and the last sheet is the one you want the code to ignore, we write code to loop through sheets 1 to 7.

  • Re: List Same Cell From Multiple Worksheets



    Hi Skywriter, thanks for responding


    For where it should start - same as the original code: a2, b2 etc. to accommodate for headers


    For part 2: Is there a way to set it to all sheets before the activesheet? Then if I have to add sheets afterward I won't have to change the loop sequence(?)


    Hope this is enough

    Thanks!

  • Re: List Same Cell From Multiple Worksheets


    Please don't quote my whole post when responding. If you have something relevant then do as I did below and quote that part, but not the whole post.


    Quote

    For where it should start - same as the original code: a2, b2 etc. to accommodate for headers


    The original code puts the values in column A going down.


    The original code puts the values in a sheet named sheet1, if that's not what you want you need to specify.


    Regarding your last question if there are a certain number of sheets that you want the code to run against, then put them at the beginning.


    If, as I explained these are sheets 1 to 7 then we make a loop from 1 to 7.


    If you want to add sheets that don't need to be looped through then put them after sheet 7 and the code doesn't need to be changed, otherwise, yes the code will have to be changed.


    If you want to control which sheets do or don't get looped through then there has to be either a list on a worksheet, of the sheets to be included or excluded which would have to be updated, or the sheet names would have to be written into the code or, we use the method I mentioned above or something unique with the names of the sheets to be looped through that the code could recognize. For example each sheet name starts with the same prefix that the sheets to be excluded don't contain, or a number, something unique.


    Quote

    For part 2: Is there a way to set it to all sheets before the activesheet? Then if I have to add sheets afterward I won't have to change the loop sequence(?)


    Yeah we could do it that way also if you want.

  • Re: List Same Cell From Multiple Worksheets


    Well, it's late where I am so I'll give you this code and we can talk about other alternatives, if this won't work for you.


    So with this code you activate a sheet and the code will loop all the sheets before the active sheet. It will put the values from cell C1 of each sheet into column A of a sheet named sheet1 starting in A2 and going down the column.

  • Re: List Same Cell From Multiple Worksheets


    Hello
    or a mash up of both

  • Re: List Same Cell From Multiple Worksheets


    Here's four different possibilities.


    There are two versions of Pike's code and two versions of mine.


    The line with the apostrophe at the beginning gives a brief description of the differences.


    One version of each adds the values down column A and one version of each adds the values across row2.


    If you can't figure out how to modify any of these to do exactly what you want, just ask, any modifications are probably pretty simple since the majority of any code needed is already written.


    Hopefully I didn't make some silly mistake, but to be safe, always test the code on a copy of your workbook.


  • Re: List Same Cell From Multiple Worksheets


    The first one you posted worked great Skywriter - it even pasted as values only, cleaner than the original code, so thank you very much, I appreciate it. Thanks for your help as well pike!


    Do I have to mark this thread as solved or do I just let it float to the next pages of the forum?

  • Re: List Same Cell From Multiple Worksheets


    My pleasure, thanks for the feedback. :cool:


    Quote

    Do I have to mark this thread as solved or do I just let it float to the next pages of the forum?


    On another forum I've personally started a post on, there's a menu item under thread tools that you can select to mark the post as solved.


    If you look above your very first post there's a drop down box labeled thread tools, it would be in there if it exists on this board.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!