+ Reply to Thread
Results 1 to 9 of 9

Copying only certain cells from all workbooks in a folder and placing them in a list

  1. 06-20-2010, 04:22 PM #1
    marymary
    marymary is offline
    Registered User
    Join Date
    06-20-2010
    Location
    Florida
    MS-Off Ver
    Excel 2007
    Posts
    5

    Copying only certain cells from all workbooks in a folder and placing them in a list

    Hi there, new poster here with a bit of a problem! I’ve been searching all over the internet and here and can’t find out how to exactly do what I need, though from other posts I think it’s possible. I want to be able to have a master workbook that pulls certain cells from all workbooks in a folder and put them in certain cells in the master workbook. I just can’t figure out the code to put to make it work. There are only three cells I need to pull from each workbook: tutor’s last name (C2), tutor’s first name (C3), and hours (C4). These cells need to go into columns A,B, and C, respectively, of the master workbook in order to have a list of all tutors and how many hours they tutored so they can be paid accordingly. Another thing is that on the master workbook the area where the info is to be placed starts on the 8th row. I don’t know if that complicates things or not, but if it does I can redo the layout.
    The organization I work for has so many papers right now that too much time is wasted and there are still errors since it is done manually which means that, unfortunately, sometimes tutors don’t get paid what they deserve. If anyone knows how to do this or can point me in the right direction I would really appreciate it! I attached the links to 3 spreadsheets, 1 master workbook and 2 individual tutor sheets. I use Excel 2007 by the way. And again, if this has already been asked/done just post a link and I’ll try to figure it out before bothering again!
    Attached Files Attached Files
    Last edited by marymary; 06-24-2010 at 05:05 PM.
    Reply With QuoteRegister To Reply
  2. 06-20-2010, 05:24 PM #2
    snb
    snb is offline
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Copying only certain cells from all workbooks in a folder and placing them in a l

    If the filenames are known you can put this formula in cell A8

    ='E:\OF\[adressen.xls]Sheet1'!C2

    in B8

    ='E:\OF\[adressen.xls]Sheet1'!C3

    in C8

    ='E:\OF\[adressen.xls]Sheet1'!C4
    in A9:C9
    ='E:\OF\[adressen1.xls]Sheet1'!C2
    ='E:\OF\[adressen1.xls]Sheet1'!C3
    ='E:\OF\[adressen1.xls]Sheet1'!C4
    Reply With QuoteRegister To Reply
  3. 06-21-2010, 08:49 PM #3
    marymary
    marymary is offline
    Registered User
    Join Date
    06-20-2010
    Location
    Florida
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Copying only certain cells from all workbooks in a folder and placing them in a l

    Yeah filenames are a problem for me because they are not always known and the number of files varies each period so it has to include all files in a folder. Thanks for taking the time though, much appreciated.
    Reply With QuoteRegister To Reply
  4. 06-22-2010, 03:41 AM #4
    snb
    snb is offline
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Copying only certain cells from all workbooks in a folder and placing them in a l

    You can do the same trick after an inventory of files in a certain folder

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  5. 06-22-2010, 06:05 PM #5
    marymary
    marymary is offline
    Registered User
    Join Date
    06-20-2010
    Location
    Florida
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Copying only certain cells from all workbooks in a folder and placing them in a l

    So I did some more research and found a macro by Jerry Beaucaire that seems to be what I need. I updated my master workbook and put a button that runs the macro, but once I choose the folder in the dialog box I get a run-time error 424 object required. Again, I need to pull cells C2, D2, and E2 from every workbook in a folder and place the cell contents of C2 into column A of the master sheet starting with row 8, D2 into column B of the master sheet starting with row 8, and E2 into column C of the master sheet starting with row 8. Also, I currently have the macro set to open all .xlsx files though I would like to add all .xls files as well. What changes would I need to make? I attached the update master workbook and if anyone could help with either problem I would greatly appreciate it!


    Please Login or Register  to view this content.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  6. 06-23-2010, 04:31 PM #6
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Copying only certain cells from all workbooks in a folder and placing them in a l

    A few tiny tweaks:
    Please Login or Register  to view this content.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)
    Reply With QuoteRegister To Reply
  7. 06-24-2010, 05:04 PM #7
    marymary
    marymary is offline
    Registered User
    Join Date
    06-20-2010
    Location
    Florida
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Copying only certain cells from all workbooks in a folder and placing them in a l

    Thanks so much JBeaucaire! It worked perfectly and I even tweaked the code a little more! I took off the part that sends the imported files to a new folder since I didn't need that and made it import all .xlsx and .xls files in a folder. I also used another code you made to sort the imported data by last names. Thanks to both of you for the help, you saved me a great deal of time! Here's the code for both macros in case anyone needs to take a look:

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  8. 06-25-2010, 02:24 AM #8
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Copying only certain cells from all workbooks in a folder and placing them in a l

    Fix this:
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  9. 06-25-2010, 12:46 PM #9
    marymary
    marymary is offline
    Registered User
    Join Date
    06-20-2010
    Location
    Florida
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Copying only certain cells from all workbooks in a folder and placing them in a l

    Oh yeah, good catch. I don't know why I ended it by adding the 47 after the C. Thanks again!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1