+ Reply to Thread
Results 1 to 8 of 8

Upload data to Specified columns in Closed Workbook - if name in column A matches

  1. #1
    Forum Contributor
    Join Date
    07-23-2019
    Location
    San Antonio, Texas
    MS-Off Ver
    Office 365
    Posts
    113

    Upload data to Specified columns in Closed Workbook - if name in column A matches

    Good evening.

    I am needing some help please. I have attached two separate workbooks ("Templates") --Monitoring Report and Analytical Data.

    1. I upload the analytical data to the Analytical Template (which does have formulas in columns C & E - S).

    What I need is:

    2. I need to copy the final data from columns B - S of the Analytical Data WB - ABC Stage WS
    and Paste that data (as numbers, not as formulas) to columns H - Z of the Monitoring Report WB - Treat Summary WS
    a. There may be data in columns B - G of the Monitoring Report, which will need to stay intact (as is)
    b. The pasted data will need to copy to the row that matches the names from column A of the Analytical Data to column A of the Monitoring Report.


    Example:
    Column A of the Monitoring Report - Treat Summary WS (beginning at Cell A9) has a list of 5 well names
    AAA-1, AAA-2, LuLu, 1-12 5WA, 1-12 3BS

    Column A of the Analytical Report - ABC Stage WS (beginning at Cell A2) has a list of 4 well names
    AAA-1, AAA-2, 1-12 5WA, 1-12 3BS

    I need the data from the AAA-1 data in the Analytical Report to upload the the row that has AAA-1 of the Monitoring Report


    I tried to write the following macro, but it is not running the way I had hoped:

    Please Login or Register  to view this content.


    I appreciate your help and time in resolving this request.

    Kindest Regards,
    Paula
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,757

    Re: Upload data to Specified columns in Closed Workbook - if name in column A matches

    If full path & sheet name is known...
    Please Login or Register  to view this content.

  3. #3
    Forum Contributor
    Join Date
    07-23-2019
    Location
    San Antonio, Texas
    MS-Off Ver
    Office 365
    Posts
    113

    Re: Upload data to Specified columns in Closed Workbook - if name in column A matches

    My favorite genius - Jindon .

    I will give this code a try (fingers are crossed). Thank you sooooo much.

    Kind Regards,

  4. #4
    Forum Contributor
    Join Date
    07-23-2019
    Location
    San Antonio, Texas
    MS-Off Ver
    Office 365
    Posts
    113

    Re: Upload data to Specified columns in Closed Workbook - if name in column A matches

    Hello again,

    As before, I am confused. The code you provided does not define the path of the closed workbook (Monitoring Report_2020_Test-1), so how does the code know to open that workbook and dump the data from the Analytical Data_Test1 WB to the Monitoring Report_2020_Test-1?

    Thank you for your patience and help.

    Paula

  5. #5
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,757

    Re: Upload data to Specified columns in Closed Workbook - if name in column A matches

    1) Full path will be given when you select the file.
    2) It will not open the workbook that is selected by 1).
    3) Get the data via formula and will be converted to value.

    I'm out at the moment.
    Is it working or not?

  6. #6
    Forum Contributor
    Join Date
    07-23-2019
    Location
    San Antonio, Texas
    MS-Off Ver
    Office 365
    Posts
    113

    Re: Upload data to Specified columns in Closed Workbook - if name in column A matches

    Hello again,

    So sorry, but I'm still very confused. The macro you provided is not working for me, but it may be because of my lack of understanding. So, for clarification:

    I am not using the original code you provided, which is posted in the first thread, right?
    I am only using the new code you provided, in the second thread, right?

    I added some comments in the code you provided (see below), so maybe you could help me better understand how and where that information fits into your code:
    I provided the "from and to" paths and a questions regarding the "Chr 34.

    I am not sure where I am supposed to put the closed workbook and worksheet path in the code you provided

    Sorry for my ignorance and thank you for your help and patience.

    Please let me know if I can provide any additional information.

    Kind Regards,
    Paula

    HTML Code: 

  7. #7
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,757

    Re: Upload data to Specified columns in Closed Workbook - if name in column A matches

    1) fn is to just get the full path for using in the formula in a cell, not for opening the workbook, so whatever the file selected will be kept closed.
    2) When Match function used, data types of search item and the matching Range must be identical.
    If search item is not a Number, you should add double quotes like Match("ABC",A1:A100,0), Chr(34) is a double quote.

    I upload the code after I test it most of the time where I can test.
    See attached.
    Attached Files Attached Files

  8. #8
    Forum Contributor
    Join Date
    07-23-2019
    Location
    San Antonio, Texas
    MS-Off Ver
    Office 365
    Posts
    113

    Re: Upload data to Specified columns in Closed Workbook - if name in column A matches

    good afternoon Jindon,

    I get it now. I was pasting your code in the Analytical workbook, not the Monitoring workbook...but now I see how it works. Thank you bunches for the explanation and additional help.

    You are Awesome!

    Paula

+ 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. vba to copy specific columns with unique column records to a closed workbook
    By JEAN1972 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-11-2017, 01:00 AM
  2. Replies: 5
    Last Post: 02-10-2016, 02:20 PM
  3. Replies: 0
    Last Post: 03-27-2014, 12:38 PM
  4. Replies: 1
    Last Post: 09-26-2013, 12:16 PM
  5. Replies: 1
    Last Post: 09-26-2013, 11:32 AM
  6. Replies: 12
    Last Post: 07-03-2013, 01:35 PM
  7. Replies: 18
    Last Post: 12-13-2011, 10:15 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