+ Reply to Thread
Results 1 to 5 of 5

Copy Cells Down from Different Tab Names in Order

  1. #1
    Forum Contributor
    Join Date
    11-25-2012
    Location
    Edmonton, Alberta
    MS-Off Ver
    2010
    Posts
    146

    Copy Cells Down from Different Tab Names in Order

    Hi,
    Sorry about title, hard to explain in a few words...
    I have a sheet with multiple tabs, Squat (1), Bench (1), Deadlift (1), Squat (2) etc. I would like to put the value from B8 and B5 from each of those sheets (and new ones that have not yet been created).
    This can be done manually, but will end up being quite tedious to create each time a new sheet is added. Currently I have this formula ("Squat" is replace with "Bench" and "Deadlift" depending the cell)
    Please Login or Register  to view this content.
    Now when I copy downward that is when it breaks as I keep getting the "row" increasing in number but it out paces the sheet numbers.
    Attached is the workbook.Daily Workout.xlsx
    Thanks

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,013

    Re: Copy Cells Down from Different Tab Names in Order

    See if this approach will help, it relies on having a UDF (User Defined Function) to pull in existing (and new) sheet names, which can then be references by the INDIRECT()

    1. create a named name, and call it SheetNames
    2. in the Refers To, enter this...
    =REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")
    3. Put this where you want the sheet names to be (I used column A and moved your stuff over 1 column)
    =IFERROR(HYPERLINK("#"&"'"&INDEX(Sheetnames,ROWS($A$2:A2))&"'!A1",INDEX(Sheetnames,ROWS($A$2:A2))),"")
    copy down as needed
    4. change your formulas to reference column A instead of hard coding the sheet name...
    =IFERROR(INDIRECT("'"&A3&"'!b5"),"")

    See how you get on with that?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Contributor
    Join Date
    11-25-2012
    Location
    Edmonton, Alberta
    MS-Off Ver
    2010
    Posts
    146

    Re: Copy Cells Down from Different Tab Names in Order

    Hi FDibbins,
    Thanks for your reply, sorry wasn't able to get to it until now, I will try out your solution and let you know!
    Thanks

  4. #4
    Forum Contributor
    Join Date
    11-25-2012
    Location
    Edmonton, Alberta
    MS-Off Ver
    2010
    Posts
    146

    Re: Copy Cells Down from Different Tab Names in Order

    Hi FDibbins,
    It always amazes me how smart you guys on here are! It took me some googling on how to create the named names etc but seemed to have figured it out and it works perfectly!
    Thanks for your help!
    Last edited by groundin; 12-29-2015 at 01:31 PM. Reason: spelling

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,013

    Re: Copy Cells Down from Different Tab Names in Order

    Happy to help, thanks for the kind words and the feedback

    Sorry I did not include how to create a named range, I guess sometimes we just assume stuff

+ 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] Copy cells X number of times [ Without Changing the order ]
    By dhilipan in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-22-2015, 08:30 AM
  2. Replies: 4
    Last Post: 12-02-2014, 03:46 AM
  3. Replies: 1
    Last Post: 12-01-2014, 03:01 PM
  4. Lookup and copy last months order BUT have similar names
    By king10001 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-23-2013, 05:39 AM
  5. To put in Top 10 order then copy / paste the cells belongs to
    By dj_danu01 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-26-2013, 05:56 AM
  6. Reshuffling of worksheet names depending on the order of cells in a list + more
    By joshnathan in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-05-2012, 09:06 AM
  7. Copy cells in a determined order
    By pmarques in forum Excel General
    Replies: 3
    Last Post: 02-06-2006, 12:15 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