+ Reply to Thread
Results 1 to 3 of 3

VBA to match value in column, offset by 1 and then copy all rows to end

  1. #1
    Registered User
    Join Date
    05-02-2018
    Location
    Brighton, England
    MS-Off Ver
    Excel 2016
    Posts
    18

    VBA to match value in column, offset by 1 and then copy all rows to end

    Hi,

    I currently have the below macro that will copy data from one sheet to another,

    At the moment the below code will copy all cells from A2:DT in workbook 2 and paste at A2 in workbook 1. It the deletes all data in workbook 2 ready for new data to be inputted.

    Please Login or Register  to view this content.
    Due to circumstances I am no longer able to delete data in workbook 2 however if I were to continue as is I would end up with duplicates in workbook 1.

    All rows are assigned a specific value increase from 1 to infinity that is populated in column A and refered to as Sales ID

    I have a cell located in workbook 1 that has a max formula that returns the last posted Sales ID (Currently a 4879 and located at Cell M3 of sheet 4)

    Essentially what I need to do is have the macro refer to this cell, match the cell in Workbook 2 and then copy from the row below to the last row.

    I think I need something along the lines of the below to reference the value that I want to match however I cannot work out how to then search through column A in workbook 2 and then only copy rows below

    Please Login or Register  to view this content.
    If there is somthing simple I am missing please let me know!

    Thanks

  2. #2
    Forum Expert
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    1,569

    Re: VBA to match value in column, offset by 1 and then copy all rows to end

    Maybe something like this

    Please Login or Register  to view this content.
    Last edited by JLGWhiz; 09-13-2019 at 05:07 PM.
    Any code provided by me should be tested on a copy or a mock up of your original data before applying it to the original. Some events in VBA cannot be reversed with the undo facility in Excel. If your original post is satisfied, please mark the thread as "Solved". To upload a file, click the 'Go Advanced' button, Then click 'Manage Attachements' and follow the menu.
    Thinking I am smart and knowing I am smart are two entirely different levels of schizophrenia.

  3. #3
    Registered User
    Join Date
    05-02-2018
    Location
    Brighton, England
    MS-Off Ver
    Excel 2016
    Posts
    18

    Re: VBA to match value in column, offset by 1 and then copy all rows to end

    Awesome, that's worked perfectly!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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