+ Reply to Thread
Results 1 to 15 of 15

Copy and Paste after matching a cell

  1. #1
    Forum Contributor
    Join Date
    09-25-2011
    Location
    Lenasia, Johannesburg,South Africa
    MS-Off Ver
    Excel 2019
    Posts
    269

    Copy and Paste after matching a cell

    Greetings

    My previous request i had twos separate files which was confusing.

    I have a template which is made up of two worksheets.

    The first one is called racescrape and the second racedata

    The racedata worksheet is madeup of an identifier called Race-1 and will contain data column a2 to colm E23.

    Race-2 will be in Column A24 and data will be in column A25 to E46 ...... Race-10 column A208 and data A208 .. E 231.

    as in the template some may be blank.


    First I would like the data under identifiers to be cleared.

    Second, the worksheet Racescrape contains data as follows:
    A1 the identifier Race-1 and in B1 time
    A2 blank and
    A3 has headers
    A4 contain data until E last row


    I need help with writing a macro that will

    1. clear the data areas from worksheet racedata keeping identifiers intact

    and therafter copy data from worksheet racescrape firstly matching the identifier and copy the data from after the header in Column A to column E last row to the corresponding identifier in the worksheet racedata.

    Trust that made myself a little clearer and thank all for help received.

    kind regards

    rajann


    PS the data under the identifiers in racescrape can fluctuate where as the identifier in the worksheet racedata will be fixed.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,910

    Re: Copy and Paste after matching a cell

    I would forget about using two sheets, and create the sheet racedata from the sheet racescrape:

    Please Login or Register  to view this content.
    Bernie Deitrick
    Excel MVP 2000-2010

  3. #3
    Forum Contributor
    Join Date
    09-25-2011
    Location
    Lenasia, Johannesburg,South Africa
    MS-Off Ver
    Excel 2019
    Posts
    269

    Re: Copy and Paste after matching a cell

    Hi

    Thank you very much for the solution which is working like a charm

    I like the way where you use one sheet only


    I need some help if its possible on this which is a part of my project

    I attach a file if it can be fixed for me please

    Kind regards

    Rajann
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,910

    Re: Copy and Paste after matching a cell

    Try this macro - it was unclear what you wanted to do with A, B, and Q....

    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    09-25-2011
    Location
    Lenasia, Johannesburg,South Africa
    MS-Off Ver
    Excel 2019
    Posts
    269

    Re: Copy and Paste after matching a cell

    Hi

    Excuse me for not making myself clear

    I made some comments on my file which am attaching trust that this may make it clear

    Kind regards

    and thank once again for the help in solving my macros

    regards

    rajann
    Attached Files Attached Files

  6. #6
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,910

    Re: Copy and Paste after matching a cell

    Just change all of your formulas to include a conditional:

    =IF($A3=0,"", OLD FORMULA without the = )

    For example:

    =IFERROR(MOD((F$2+D3)-1,9)+1,"")

    to

    =IF($A3=0,"",IFERROR(MOD((F$2+D3)-1,9)+1,""))

    and then don't use the macro.

  7. #7
    Forum Contributor
    Join Date
    09-25-2011
    Location
    Lenasia, Johannesburg,South Africa
    MS-Off Ver
    Excel 2019
    Posts
    269

    Re: Copy and Paste after matching a cell

    Hi

    Thank you so much for the help

    Its helping me a greatly

    Kind regards

    Rajann

  8. #8
    Forum Contributor
    Join Date
    09-25-2011
    Location
    Lenasia, Johannesburg,South Africa
    MS-Off Ver
    Excel 2019
    Posts
    269

    Re: Copy and Paste after matching a cell

    Hi

    Thank you for solving my problem

    I have another request if its possible.

    The macro creates a worksheet called racedata and copy the data from racescrape into racedata

    After this can the macro be amended to create a second worksheet called datasplit

    In this worksheet the data from racescrape must be copied like in the racadata but the column D must
    be copied into column column H . in datasplit and the column E from racescrape must be copied into
    column L of the datasplit worksdheet


    or after racedata is created, a further worksheet called datasplit must be created and the data from
    columns A,B and C must be copied into columns A,B and C of datasplit. Column D from racedata must be copied
    into column H of datasplit and column and column E from Racedata must be copied into column L of the worksheet datasplit.

    The worksheet datasplit must not be erased when the macro is run again.

    Trust I made myself clera

    K Ind regards

    rajann

  9. #9
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,910

    Re: Copy and Paste after matching a cell

    "The worksheet datasplit must not be erased when the macro is run again."

    That will be easy, but where do you want the new values to be placed when the macro is run again? On a different sheet, or at the bottom of the filled columns, or.....

  10. #10
    Forum Contributor
    Join Date
    09-25-2011
    Location
    Lenasia, Johannesburg,South Africa
    MS-Off Ver
    Excel 2019
    Posts
    269

    Re: Copy and Paste after matching a cell

    I have attached the Datasplit worksheet.

    The data from Racedata will be copied into the shaded area of the datasplit worksheet

    The Datasplit worksheep will have formula that must not be erased.

    Trust thta this will help when structuring the macro

    After racadata has been created, the macro will copy from racedata to datasplit

    whenever the macro runs it will erase only the racedata worksheet

    regards

    rajann
    Attached Files Attached Files
    Last edited by khanaran; 05-24-2019 at 03:37 PM. Reason: fix

  11. #11
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,910

    Re: Copy and Paste after matching a cell

    Now I'm confused - you said you want the macro to create the sheet datasplit - but then only overwrite the data. I am assuming that you have a sheet named datasplit available with whatever formatting and formula you need, and that instead of copying from racescrape you can copy from racedata beacuse the first macro does the job of spacing the data out consistently.

    Just add these two lines at the bottom of the macro, before the End Sub statement:



    Please Login or Register  to view this content.

  12. #12
    Forum Contributor
    Join Date
    09-25-2011
    Location
    Lenasia, Johannesburg,South Africa
    MS-Off Ver
    Excel 2019
    Posts
    269

    Re: Copy and Paste after matching a cell

    Hi

    Thanx for all the help and do apologies for causing the confusion.

    I would have a datasplit worksheet

  13. #13
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,910

    Re: Copy and Paste after matching a cell

    Let me know if the code works for you - always happy to make tweaks, but it may not be until Tuesday ;-)

  14. #14
    Forum Contributor
    Join Date
    09-25-2011
    Location
    Lenasia, Johannesburg,South Africa
    MS-Off Ver
    Excel 2019
    Posts
    269

    Re: Copy and Paste after matching a cell

    HI

    Thank you so much for working with me in order to solve my issues

    Will test over the weekend and revert back to you on tuesday

    kind regards

    rajann

  15. #15
    Forum Contributor
    Join Date
    09-25-2011
    Location
    Lenasia, Johannesburg,South Africa
    MS-Off Ver
    Excel 2019
    Posts
    269

    Re: Copy and Paste after matching a cell

    Hi

    The help is very good.

    I was able over the weekend to create an additional sheet and copy data into the sheet automatically

    I need help with some tweaking:-

    I am able to copy certain data from Datasplit to Analysis.

    Is it possible for column to arrange the data i Analysis worksheet in order from lowest to highest in Column A for each set of data.

    it much be able to sort the range using col A as the sort key


    1. after running the macro, can it write a file in the same folder when we give it a file name and current date by asking for a name to give to the workbook.

    And

    2 secondly after the macro runs can the next step be to write the workbook into the same folder, asking for a filename


    Thanx once again for the help

    Rajann
    Attached Files Attached Files

+ 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. [SOLVED] Find matching data, copy data in adjacent cell and paste in new column
    By Bikeman in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 06-23-2015, 04:08 PM
  2. Copy paste values to a cell with matching date
    By Beggar in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-03-2014, 12:49 AM
  3. Replies: 1
    Last Post: 09-03-2014, 12:46 AM
  4. [SOLVED] Align matching columns, then copy and paste data from one cell into another
    By Allisaursus in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-06-2013, 12:33 PM
  5. Copy and paste matching data.
    By davidparkes in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-01-2011, 04:19 PM
  6. Copy/Paste to Specific Row with matching Value
    By David in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-26-2006, 07:45 AM
  7. [SOLVED] copy and paste matching by using ID's
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-18-2005, 06:05 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