+ Reply to Thread
Results 1 to 10 of 10

Macro to Transfer Data from One Workbook to Another

  1. #1
    Valued Forum Contributor
    Join Date
    09-09-2009
    Location
    Liverpool, England
    MS-Off Ver
    W: 2019 H: 365
    Posts
    940

    Macro to Transfer Data from One Workbook to Another

    Evening all,

    We're currently undertaking a tender process and are anticipating a number of bids to be received shortly. What I would like to develop is a macro that will extract all of the bids received for each task and place them into the relevant summarised table. Essentially the steps I would like the macro to undertake are as follows:

    1. Browse and select the folder containing the files to be imported (or browse to the folder and select the files) into the summarised sheet.
    2. Extract the supplier name from the 'supplier' tab (green cell) on each 'Tender Submission Document' workbook and copy it into the first empty cell in the relevant range (again highlighted green) on each of the 'Bids Task x' tabs (Bids Received Summary workbook).
    3. Extract the rates bid for each task (blue cells - 'Tender Submission Document' workbook) and copy them to the relevant tab and cells (aligned to the correct supplier) in the 'Bids Received Summary' workbook (again highlighted blue).

    I've attached an example of the bid submission and summary document that I'm working with.

    I hope this makes sense? If you have any questions do not hesitate to contact me.

    Regards,

    Snook
    Attached Files Attached Files
    Last edited by The_Snook; 02-17-2017 at 07:59 AM.

  2. #2
    Valued Forum Contributor kasan's Avatar
    Join Date
    07-22-2009
    Location
    Riga, Latvia
    MS-Off Ver
    Excel 2010
    Posts
    680

    Re: Macro to Transfer Data from One Workbook to Another

    Hi,
    Unfourtunatelly both of uploaded files are empty. If you could fill tables in your files with some numbers - it would be better for understanding.
    You will have several "Tender Submission Documents", right? Does all source files have the same structure? Supplier name is always on the sheet "Supplier" in cell A10?

  3. #3
    Valued Forum Contributor
    Join Date
    09-09-2009
    Location
    Liverpool, England
    MS-Off Ver
    W: 2019 H: 365
    Posts
    940

    Re: Macro to Transfer Data from One Workbook to Another

    Hi kasan,

    Updated as requested.

    Yes we're anticipating ~100 returned tender submissions and each document should be in the same structure. The supplier name is always located on the 'supplier' tab in cell A10.

    Regards,

    Snook

  4. #4
    Valued Forum Contributor kasan's Avatar
    Join Date
    07-22-2009
    Location
    Riga, Latvia
    MS-Off Ver
    Excel 2010
    Posts
    680

    Re: Macro to Transfer Data from One Workbook to Another

    Hi,
    Code below should collect info from all .xlsx files from folder C:\abc\. You can change it if you like (change red code).
    This is build assuming that there are only 3 tasks and all sheets in all files are names as "Task 1", "Task 2", "Task 3".
    Attached two files with data (should be placed in folder) and file to combine all data from that files.
    Please Login or Register  to view this content.
    Attached Files Attached Files

  5. #5
    Valued Forum Contributor
    Join Date
    09-09-2009
    Location
    Liverpool, England
    MS-Off Ver
    W: 2019 H: 365
    Posts
    940

    Re: Macro to Transfer Data from One Workbook to Another

    Cheers kasan, I'll give it a whirl and will let you know how I get on.

    Thanks again,

    Snook

  6. #6
    Valued Forum Contributor
    Join Date
    09-09-2009
    Location
    Liverpool, England
    MS-Off Ver
    W: 2019 H: 365
    Posts
    940

    Re: Macro to Transfer Data from One Workbook to Another

    It works an absolute treat kasan!

    Thank you!

    Snook

  7. #7
    Valued Forum Contributor
    Join Date
    09-09-2009
    Location
    Liverpool, England
    MS-Off Ver
    W: 2019 H: 365
    Posts
    940

    Re: Macro to Transfer Data from One Workbook to Another

    Afternoon all,

    Kasan kindly provided the above code for me which does what I need. However, I'd like to amend it slightly so that the data import range is cleared each time the macro is run (so duplicate data isn't simply being added to the bottom of the data range each time the macro is run). How can I tweak it so that as part of the looping process it clears the range A5:G250 on each tab (or whatever the range is for the existing data already there - I don't mind either way). I've tried a myriad of ways but I keep getting error messages.

    Thanks in advance,

    Snook

  8. #8
    Valued Forum Contributor kasan's Avatar
    Join Date
    07-22-2009
    Location
    Riga, Latvia
    MS-Off Ver
    Excel 2010
    Posts
    680

    Re: Macro to Transfer Data from One Workbook to Another

    Hi,
    If I got it right - you need to clear your Summary data before you import new data.
    What error messages do you get?
    Basic clear of contents can be like this (50000 is just a number from the air, assuming you never will get this count of rows after any import).
    Place this lines of code at the beginning of your macro (before loop starts).
    Please Login or Register  to view this content.
    Didn't get why you want to make this clear "as part of the looping process"?

  9. #9
    Valued Forum Contributor
    Join Date
    09-09-2009
    Location
    Liverpool, England
    MS-Off Ver
    W: 2019 H: 365
    Posts
    940

    Re: Macro to Transfer Data from One Workbook to Another

    Cheers kasan, I think I was trying to be too clever!

    I'm now trying to build some conditional formatting into my imported data but I'm encountering a 'select method of range class failed' error message when I run the macro. I've added the following code after the loop:

    Please Login or Register  to view this content.
    Any idea where I'm going wrong? If it helps it is the first line of the code that it seems to have an issue with (it is highlighted in yellow).

    Cheers,

    Snook

  10. #10
    Valued Forum Contributor
    Join Date
    09-09-2009
    Location
    Liverpool, England
    MS-Off Ver
    W: 2019 H: 365
    Posts
    940

    Re: Macro to Transfer Data from One Workbook to Another

    I think I've solved it. If I add summ.Sheets("Bids (Task 1)").Activate at the start of the code that seems to rectify it.

    Snook

+ 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. Macro that will transfer data into single workbook
    By K_Rene in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-12-2016, 09:33 AM
  2. [SOLVED] How to transfer data from one workbook to another workbook using Macro
    By kingsdime29x in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-13-2016, 12:27 PM
  3. Replies: 3
    Last Post: 02-21-2014, 09:05 AM
  4. [SOLVED] need help on a macro to transfer data to another workbook
    By aftonr in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-10-2012, 11:47 AM
  5. Macro to transfer data from one workbook to another
    By Sean Anderson in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 05-16-2011, 01:37 PM
  6. Transfer data fron one workbook to another using macro
    By nachousa in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-11-2010, 11:46 AM
  7. Transfer data fron one workbook to another using macro
    By nachousa in forum Access Programming / VBA / Macros
    Replies: 1
    Last Post: 06-11-2010, 11:45 AM

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