+ Reply to Thread
Results 1 to 11 of 11

Sort by specific cell content, then cut and paste to a new sheet

  1. #1
    Registered User
    Join Date
    04-20-2013
    Location
    Los Angeles
    MS-Off Ver
    Excel 2010
    Posts
    96

    Sort by specific cell content, then cut and paste to a new sheet

    Hi there,

    I need help with this macro. Attached is a sample Book showing a small number of rows and columns with content in Sheet1, my actual files might contain at least a few hundred rows and a variable number of columns.

    What I need, is code that: 1)Finds cells containing "(LK)" in Column C (will always be this column), regardless of any other content in the cell, then, 2)Any rows meeting that criteria, I need them cut and pasted to a new Sheet(2), starting in row A2, so the headers from row 1 on the original sheet are copied and pasted with these new columns, then 3)The contents of this new sheet are sorted A-Z by Column C.

    The content on the second sheet of my attached sample file show what I'm looking to achieve.

    Thanks for any help!

    NOTE: The name of the sheet I'll be working with will always be different so it cannot be referred to as "Sheet1" within the code. Guess 'Active Sheet' does the trick?

    David
    Attached Files Attached Files

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

    Re: Sort by specific cell content, then cut and paste to a new sheet

    Try
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    04-20-2013
    Location
    Los Angeles
    MS-Off Ver
    Excel 2010
    Posts
    96

    Re: Sort by specific cell content, then cut and paste to a new sheet

    Hi Jindon,

    Thank you so much for your help. I tried the code but, unfortunately, it gets stuck after copying the found data onto the clipboard. I get this message from Excel: "Run-time error '9': Subscript out of range". Not sure what's going on?

    When clicking on "Debug", Excel highlights this line within the code: .Copy Sheets("sheet2").Cells(1)

    Thanks for your help!

  4. #4
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Sort by specific cell content, then cut and paste to a new sheet

    No error...................
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    04-20-2013
    Location
    Los Angeles
    MS-Off Ver
    Excel 2010
    Posts
    96

    Re: Sort by specific cell content, then cut and paste to a new sheet

    Hi again Jindon,

    Thank you for your help with this. I was able to find out why my "real life" file still gets stuck: it's missing a header for the last column, which made me realize I wasn't taking into account my number of columns will be different each time. Could you please help me adding code that adds headers for as many columns as the file might have? The header itself just needs to be a sequence like this: LOCATION 2, LOCATION 3, etc. That is, the current headers on my sample book are fine (C1 as "LOCATION(S)", and D1 as "(BIN LOCATION)", any additional column can start with LOCATION 2 and so on.

    Thanks for your help, really appreciate it.

  6. #6
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Sort by specific cell content, then cut and paste to a new sheet

    It would not raise such error just for missing header.

    Upload the workbook that you had such error with just dummy data.

  7. #7
    Registered User
    Join Date
    04-20-2013
    Location
    Los Angeles
    MS-Off Ver
    Excel 2010
    Posts
    96

    Re: Sort by specific cell content, then cut and paste to a new sheet

    Hi Jindon,

    Further testing revealed the root of the issue: code won't work if there's no "Sheet2" already created. I din't take that into account when formulating the question. The workbook I start with, always has a single sheet so any additional sheet has to be added.

    I added the below code which seems to work fine. However, I wasn't able to set that the new sheet is added 'after' the active sheet. I tried some code I found online, but it didn't work.

    How do I do that? Is this the better way to go when adding a sheet?

    Thanks for your help.


    Set aws = ActiveSheet
    Sheets.Add
    ActiveSheet.Name = "Sheet2"
    aws.Activate

  8. #8
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Sort by specific cell content, then cut and paste to a new sheet

    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    04-20-2013
    Location
    Los Angeles
    MS-Off Ver
    Excel 2010
    Posts
    96

    Re: Sort by specific cell content, then cut and paste to a new sheet

    Hi Jindon,

    The code works as intended! Thank you so much for your help! I'm marking this thread as solved and adding reputation to you.

    Now, I just posted another thread where I'm asking for help with some code that actually you also wrote for me. The code works, but I now have a challenging situation where I need to modify it to meet other needs. In case you have the time to take a look into it, I named the post "Sort data in specific alphanumerical order".

    Thanks again!

  10. #10
    Registered User
    Join Date
    04-20-2013
    Location
    Los Angeles
    MS-Off Ver
    Excel 2010
    Posts
    96

    Re: Sort by specific cell content, then cut and paste to a new sheet

    Hi Jindon!

    This is the thread. So, for the code you wrote, I just need to extend the range where the filter looks for the "*(LK)" content. I later realized that content is sometimes found in another column other than "3". So, below is the specific line I'm referring to:

    Please Login or Register  to view this content.
    Thanks!

  11. #11
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Sort by specific cell content, then cut and paste to a new sheet

    If the criteria anywhere in the range then,
    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. [SOLVED] Sort by specific column & delete rows based on content
    By axtryo in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-21-2018, 01:49 AM
  2. Replies: 6
    Last Post: 11-25-2015, 02:08 AM
  3. [SOLVED] [Solved] Conditional copy/paste cell content from one sheet to another
    By chng87 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-12-2014, 06:01 AM
  4. Copy cell content and paste into another cells in the same sheet using add button
    By Amoursi in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-04-2013, 02:28 AM
  5. copying cell content into a specific workbook based on the content in that cell
    By krishna reddy in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-20-2012, 03:52 AM
  6. based on Cell/Column content ,cut one sheet's values and paste it in other sheet?
    By mindpeace in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-25-2006, 08:33 AM
  7. based on Cell/Column content ,cut one sheet's values and paste it in other sheet?
    By mindpeace in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-24-2006, 10:33 AM

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