+ Reply to Thread
Results 1 to 8 of 8

Code to copy rows to new sheet if cell contains

  1. #1
    Registered User
    Join Date
    11-12-2017
    Location
    Stafford, England
    MS-Off Ver
    365
    Posts
    184

    Code to copy rows to new sheet if cell contains

    Hi,

    Always get confused with VBA copying to new sheet, sample file is not fully working as in buttons/macros might not work but...

    What I'm looking for is code that I can use in a macro Call Function to copy data from sheet 'Closed Adjustments' Columns B, I & L, row 6 into sheet 'Investigation' columns B, C & D row 2.

    But only if column L in sheet 'Closed Adjustments' contains a reference numbers starting with A and has 10 digits, e.g. A1234567890

    As you can see in sample file column L 'Closed Adjustments' can include many different reasons in 'L', just looking up that specific string where the number is always different but always one letter A and then 10 digits.

    Hope that makes sense.

    Tez
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 365
    Posts
    2,401

    Re: Code to copy rows to new sheet if cell contains

    Does this macro do what you want...
    Please Login or Register  to view this content.

  3. #3
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Code to copy rows to new sheet if cell contains

    Hi,

    See the attached that does what you want. I used Split and a few If statements to do a new Sub

    CopyA10Split.xlsm
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  4. #4
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 365
    Posts
    2,401

    Re: Code to copy rows to new sheet if cell contains

    Quote Originally Posted by Rick Rothstein View Post
    Does this macro do what you want...
    Please Login or Register  to view this content.
    I have reworked my original code (see above) to make it so it runs as fast as is possible (if you have a huge amount of data, the difference might probably be noticeable)...
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    11-12-2017
    Location
    Stafford, England
    MS-Off Ver
    365
    Posts
    184

    Re: Code to copy rows to new sheet if cell contains

    Thanks, your suggestions work great and just as I requested.

    Just wondering and I am sure it is possible, could just the A reference be copied over to investigation sheet and not all the other comments that might be in column L.

    Sorry for the change, its just constantly evolving.

    Tez

  6. #6
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 365
    Posts
    2,401

    Re: Code to copy rows to new sheet if cell contains

    Does this modified version of my code do what you want...
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    11-12-2017
    Location
    Stafford, England
    MS-Off Ver
    365
    Posts
    184

    Re: Code to copy rows to new sheet if cell contains

    That's perfect, thank you so much.

    Tez

  8. #8
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Code to copy rows to new sheet if cell contains

    Hey Tez

    Here is new code that will do what you ask:
    Please Login or Register  to view this content.

+ 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. code to copy first rows and sheet titles from separate csv files into summary sheet
    By freshmint in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-08-2021, 06:10 AM
  2. [SOLVED] VBA code to select rows and copy into new sheet
    By Rachel5694 in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 10-28-2018, 10:50 PM
  3. [SOLVED] copy rows out of a datasheet based on a code number and past these rows in a new sheet
    By Robert1311 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-11-2018, 11:41 AM
  4. [SOLVED] Code to copy nnon adjecent rows to another sheet
    By KevBotes in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-25-2016, 04:50 PM
  5. VBA code to copy rows to another sheet and sumup its columns
    By patua in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-24-2014, 11:59 AM
  6. Replies: 2
    Last Post: 11-05-2012, 09:28 AM
  7. Replies: 2
    Last Post: 05-25-2006, 02:55 PM

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