+ Reply to Thread
Results 1 to 11 of 11

Loop through specific worksheets and copy and past to another specific workbook/worksheet

  1. #1
    Registered User
    Join Date
    07-10-2013
    Location
    New York State
    MS-Off Ver
    Excel 2007
    Posts
    7

    Loop through specific worksheets and copy and past to another specific workbook/worksheet

    I have a workbook with 20+ worksheets. Each worksheet has a specific column (F) that I have a total and then offset by 2 rows and then multiply the total by 65%. It is this value that I need to copy. It is never in the same place for each worksheet or week to week.

    I have attempted to create a code to automate copying this new value and pasting it to a corresponding worksheet in another workbook. I am am terrible at loops and end up just copying the same code over and over and "calling" it from the previous code. For some reason what I have done this time is wreaking havoc and causing the code to run forever and eventually error. It works ok if I do n ot use the "call feature" and just run one code at a time, but something mut be wrong and I am sure there is a much easier way to accomplish this. Here is my code:

    Please Login or Register  to view this content.
    I have this code for 25 different names (worksheets). Is there any possible way to streamline this? Basically my code is saying find the last used cell in column F and move down two rows--this is because there is a space in between so it will not find the actual last cell. Then copy that value and paste just the values into the corresponding sheet in a different workbook. then go to the next sheet and do the same thing. It seems easy enough but this is the best I can figure out how to do. Any help/explanation would be so very appreciated.

    Thanks,
    Kathy

  2. #2
    Forum Expert
    Join Date
    08-02-2013
    Location
    Québec
    MS-Off Ver
    Excel 2003, 2007, 2013
    Posts
    1,412

    Re: Loop through specific worksheets and copy and past to another specific workbook/worksh

    Hi Kathy,
    I don't have your file to test, but here's a suggestion on how you could do it.
    note: no need to select the cell of sheets each time as this slows down your code.

    Please Login or Register  to view this content.
    GC Excel

    If this post helps, then click the star icon (*) in the bottom left-hand corner of my post to Add reputation.

  3. #3
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,498

    Re: Loop through specific worksheets and copy and past to another specific workbook/worksh

    Download and save both workbooks in a folder.
    Open both and run the macro in "House.xlsm" (Click button on first sheet)
    Check all sheets in "House_Other.xlsx".
    If all to your needs, copy code into your workbook.
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Experience trumps academics every day of the week and twice on Sunday.

  4. #4
    Registered User
    Join Date
    07-10-2013
    Location
    New York State
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Loop through specific worksheets and copy and past to another specific workbook/worksh

    Hello and thank you for this sample.

    I has originally received an error but figured out that I needed to have my worksheets tab be an exact match. Once I did that, it worked like a charm.

    Question: How do I convert this to do the same thing but instead of placing in B2, place in B5? I'd love the implement into several different worksheets. This would be such a time saver for me!

    Thanks for the help. I'm very excited to be able to utilize this!
    Kathy
    Last edited by KathCobb; 08-27-2022 at 01:54 PM.

  5. #5
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,498

    Re: Loop through specific worksheets and copy and past to another specific workbook/worksh

    Which post are you referring to?
    OK, I just now saw that you mentioned the attached example so it must be Post #3.

    Is that B5 a hardcoded range? It will not change because of other data?
    If it is, change this
    Please Login or Register  to view this content.
    to this
    Please Login or Register  to view this content.
    BTW, yes, you always have to make sure that there are no spelling mistakes, leading and or trailing spaces etc etc.
    Last edited by jolivanes; 08-27-2022 at 04:28 PM.

  6. #6
    Registered User
    Join Date
    07-10-2013
    Location
    New York State
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Loop through specific worksheets and copy and past to another specific workbook/worksh

    My apologies for just getting back to this. Jolivanes, everything you have helped me works great! I only have one issue. There are times when the "house_Other" workbook does not contain all worksheets that are in the "house" workbook. There are never different tabs, just sometimes there is only 15 of the 20 tabs that are in the House sheet. Is it possible to tell the code that if it does not find a matching tab it can skip it or something to that affect? That would be a great help if it is possible.

    Thank you so much for all the help you have provided so far.

    Kathy

  7. #7
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,498

    Re: Loop through specific worksheets and copy and past to another specific workbook/worksh

    Show the code you are using here please.

  8. #8
    Registered User
    Join Date
    07-10-2013
    Location
    New York State
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Loop through specific worksheets and copy and past to another specific workbook/worksh

    Hello again, I am using the code below:

    Please Login or Register  to view this content.
    For the above code to work, all worksheets in House.xlsx need to be the same as all worksheets in House_Other.xlsx. But sometimes I have tabs for Dylan, Bob and Tom in House_Other.xlsx and other times I would have tabs for Dylan, Jon and Beth in House_Other.xlsx. In House.xlsx I always have tabs for everybody, ie: Dylan, Bob, Tom, Jon and Beth. Basically, House.xlsx is the master list. It has all the worksheets/names. House_Other.xlsx is created every week and usually only has some of the names/worksheets that are always in House.xlsx. I hope that makes sense...that each week I am taking a list, breaking it into tabs by people and then trying to copy the end total into the House_Other master workbooks.

    I've tried some work arounds such as using Select Case but I could not get it to work properly. I am guessing it is because I am terrible at loops and naming rules.

    Thanks again for any help you can give.

    Kathy

  9. #9
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,498

    Re: Loop through specific worksheets and copy and past to another specific workbook/worksh

    Please Login or Register  to view this content.
    Do you have the code in "Personal.xlsb" or in a third Workbook?
    Next time, mention this as it can make a difference in suggestions supplied.

  10. #10
    Registered User
    Join Date
    07-10-2013
    Location
    New York State
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Loop through specific worksheets and copy and past to another specific workbook/worksh

    Thank you so much!! Worked perfectly! It never even occurred to me to try "On Error". I have spent hours and hours trying to come up with different work arounds for this code and others. Nor did it occur to me that it would matter where I am running the code from. It is the Personal.xlsb. I just keep my code window open and run code as needed. I really only know enough vba to be dangerous

    Thanks again. I really appreciate it!
    Kathy

  11. #11
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,498

    Re: Loop through specific worksheets and copy and past to another specific workbook/worksh

    No problem Kathy. Good to hear that all is working.
    One should not get carried away with using "On Error" but in certain instances it is fine to use it.
    If you use macros from your Peronal.xlsb, you might want to look into putting a little shortcut on the QAT (Quick Access Toolbar)
    I have a Shortcut on the QAT that opens a UserForm with Buttons that in turn run all the macros I use regularly. Quite handy.
    Good Luck

+ 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. Loop through subfolders and copy a specific file from all and past into book1.
    By saziz in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 08-22-2022, 10:07 PM
  2. [SOLVED] loop through specific worksheets instead of the entire workbook
    By mynameisnfs in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-21-2018, 08:09 PM
  3. [SOLVED] macro to copy in loop all sheets at specific row and paste in a new workbook
    By JEAN1972 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-01-2018, 03:04 AM
  4. Replies: 4
    Last Post: 02-14-2017, 11:21 AM
  5. Replies: 0
    Last Post: 07-24-2014, 09:11 AM
  6. Loop for adding specific worksheets in specific workbooks
    By pankaj8219 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-13-2013, 10:22 AM
  7. [SOLVED] Copy Specific Cells Across all worksheets in a Workbook and Paste Into single New Workbook
    By thunt13 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-14-2013, 10:43 AM

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