+ Reply to Thread
Results 1 to 15 of 15

Copy New Data from Workbook and add to bottom of sheet on another workbook

  1. #1
    Registered User
    Join Date
    12-03-2020
    Location
    United States
    MS-Off Ver
    Office 365
    Posts
    17

    Copy New Data from Workbook and add to bottom of sheet on another workbook

    Good Day All,

    I am running a report to update information over the last year. I plan on running this report maybe once a week, maybe longer in between. What I am trying to do is to take the items from the new report and compare them to the report I already have. If there is a new item, then I would like to copy the item over to the "Outstanding List".

    In the attachment, I made a sample of each workbook. I want to compare column A in each workbook and if it doesn't exist in the "Outstanding list" to copy that row from the New report over to the next available row at the bottom of the Outstanding List.

    Please let me know if you need any additional information.

    Thank you.
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    05-29-2020
    Location
    NH USA
    MS-Off Ver
    365
    Posts
    1,045

    Re: Copy New Data from Workbook and add to bottom of sheet on another workbook

    I am sure someone else could write better code, but this works for now. Running the ...Mast workbook will ask you to open your weekly report.


    Please Login or Register  to view this content.
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    12-03-2020
    Location
    United States
    MS-Off Ver
    Office 365
    Posts
    17

    Re: Copy New Data from Workbook and add to bottom of sheet on another workbook

    Thank you Maniacb. This is pretty much what I was looking for.

    Since I will be adding this to the middle of a longer script, and the files I will be referencing will already be open, I assume I could replace the "FileToOpen" line and just use a "Workbook.activate" command? Or is that line used to set the file to filter through?

    I ask because I will have 12 files open that I will have to filter through. (They will be exports from another software, 1 for each month). I was just going to copy the same script but change the active workbook. Will it be that easy?

    As for functionality, the code is exactly what I am trying to do.

  4. #4
    Valued Forum Contributor
    Join Date
    05-29-2020
    Location
    NH USA
    MS-Off Ver
    365
    Posts
    1,045

    Re: Copy New Data from Workbook and add to bottom of sheet on another workbook

    Yes, you can straight forward replace the FileToOpen with WorkBook.Activate. That part of the code only selects a worksheet. Glad to hear it worked for you

  5. #5
    Registered User
    Join Date
    12-03-2020
    Location
    United States
    MS-Off Ver
    Office 365
    Posts
    17

    Re: Copy New Data from Workbook and add to bottom of sheet on another workbook

    Good Day Maniacb,

    I was unable to edit the code to work appropriately. can you help me rescript it with both the master and the new file open. I will include the name of the workbook and worksheets I am working with.

    MasWB = ABC_Report.XLSM
    MasWS = Outstanding Lists
    OpenWB = 01_JAN.XLSX
    OpenWS = Sheet1

    I tried to alter you code but received errors. When I cleared the errors, it didn't copy the information over.

  6. #6
    Valued Forum Contributor
    Join Date
    05-29-2020
    Location
    NH USA
    MS-Off Ver
    365
    Posts
    1,045

    Re: Copy New Data from Workbook and add to bottom of sheet on another workbook

    Here is the updated code and updated workbook

    Please Login or Register  to view this content.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    12-03-2020
    Location
    United States
    MS-Off Ver
    Office 365
    Posts
    17

    Re: Copy New Data from Workbook and add to bottom of sheet on another workbook

    Excellent.

    Thank you Maniacb. I made similar changes, but I must have missed something when I edited the original suggestion. With that being said, the last solution worked as expected. I will expand to run for all my items. Thank you again.

  8. #8
    Valued Forum Contributor
    Join Date
    05-29-2020
    Location
    NH USA
    MS-Off Ver
    365
    Posts
    1,045

    Re: Copy New Data from Workbook and add to bottom of sheet on another workbook

    Glad it worked for you!

  9. #9
    Registered User
    Join Date
    12-03-2020
    Location
    United States
    MS-Off Ver
    Office 365
    Posts
    17

    Re: Copy New Data from Workbook and add to bottom of sheet on another workbook

    Jumped the gun again.

    noticed it copied over items that were already on the MasWB. Is there a way to ignore items that already are on the list?

  10. #10
    Valued Forum Contributor
    Join Date
    05-29-2020
    Location
    NH USA
    MS-Off Ver
    365
    Posts
    1,045

    Re: Copy New Data from Workbook and add to bottom of sheet on another workbook

    Try this code

    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    12-03-2020
    Location
    United States
    MS-Off Ver
    Office 365
    Posts
    17

    Re: Copy New Data from Workbook and add to bottom of sheet on another workbook

    Good Day Maniacb,

    The last hurdle is I need to count the rows in the MasWB so it pasts the new value at the bottom of the list. As it is written, it is pasting over other data (depending on the number of rows on the OpenWB.

    As of right now, and my testing, I think after "I" solve this problem, it will be good to go.

    I greatly appreciate your help on this.

  12. #12
    Valued Forum Contributor
    Join Date
    05-29-2020
    Location
    NH USA
    MS-Off Ver
    365
    Posts
    1,045

    Re: Copy New Data from Workbook and add to bottom of sheet on another workbook

    Good Day,

    Try this update

    Please Login or Register  to view this content.

  13. #13
    Registered User
    Join Date
    12-03-2020
    Location
    United States
    MS-Off Ver
    Office 365
    Posts
    17

    Re: Copy New Data from Workbook and add to bottom of sheet on another workbook

    and to limit what is copied over to only items that do not exist in the MasWB?

  14. #14
    Valued Forum Contributor
    Join Date
    05-29-2020
    Location
    NH USA
    MS-Off Ver
    365
    Posts
    1,045

    Re: Copy New Data from Workbook and add to bottom of sheet on another workbook

    This code is supposed to only copy over what does not match. Maybe you can share workbooks where this is happening, as it does work this way with the existing sample workbooks.
    Last edited by maniacb; 01-14-2021 at 07:02 PM.

  15. #15
    Registered User
    Join Date
    12-03-2020
    Location
    United States
    MS-Off Ver
    Office 365
    Posts
    17

    Re: Copy New Data from Workbook and add to bottom of sheet on another workbook

    Good Day maniacb,

    After I fiddled a little bit more, it didn't recognize the reference numbers on the MasWB because on the OpenWB, the numbers are exported as text. After converted to numbers, the script worked as expected.

    I think that officially solves my problem and I GREATLY Appreciate your time.

    Thanks Again.

+ 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 visible data and paste to the bottom of another sheet in same workbook
    By Cannon26 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-06-2019, 06:14 PM
  2. [SOLVED] Copy data from multiple WorkBook structures to the same sheet on another workbook
    By tax112 in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 09-16-2018, 09:43 AM
  3. Copy data from one workbook to a specific sheet in another workbook without overriding
    By cdhampshire in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-01-2017, 01:06 PM
  4. Match Sheet Names and Then Copy data from Workbook A to WorkBook B
    By ptmuldoon in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-16-2016, 05:08 PM
  5. Copy from one sheet into another sheet (different workbook) at the bottom
    By hawk77EF in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-14-2016, 04:01 AM
  6. [SOLVED] Copy Values From Each Workbook in Folder to a Single Sheet in New Workbook +Workbook names
    By Arsham24 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-21-2012, 07:42 PM
  7. Replies: 0
    Last Post: 08-11-2011, 01:23 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