+ Reply to Thread
Results 1 to 8 of 8

Copy/Paste Based on Character Length

  1. #1
    Registered User
    Join Date
    01-12-2011
    Location
    Michigan, USA
    MS-Off Ver
    2010
    Posts
    16

    Question Copy/Paste Based on Character Length

    I need to take all cells in column A that have two characters, copy them, and paste them to the second spreadsheet in my workbook. Seems like this should be simple enough to do, but the current segment of my code devoted to doing this isn't working as intended:
    Please Login or Register  to view this content.
    It does great in filtering the "[Corporate]" items and copying them to the third worksheet (worksheets are already assigned to ws1, ws2, and ws3), but instead of only taking cells from Column A with two characters, it takes every single cell and copies it to the second worksheet.

    The attached workbook has an example of what I would like the second tab to look like. Any help with fixing this code will be greatly appreciated.
    Attached Files Attached Files
    Last edited by sweetnumb; 01-29-2011 at 04:11 PM.

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: Copy/Paste Based on Character Length

    You're not actually filtering on the content of column A ... you're trying to filter on the length of data in column A.

    I'd suggest you add a "helper" column, say column G, and put the formula: =Len(A2) in cell G2 and copy down. Then filter on the helper column; see below:

    Please Login or Register  to view this content.

    Would've been good to have the workbook WITH the code. That's probably the reason you've had a lot of people look and no replies.

    Regards
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    01-12-2011
    Location
    Michigan, USA
    MS-Off Ver
    2010
    Posts
    16

    Re: Copy/Paste Based on Character Length

    I could have shown the rest of the macro, but it isn't very short with everything that needs to be done to this workbook, and I figured even attaching an example at all was overkill based on the task I wish this code to perform.

    Good suggestion though, and that was my original plan, but then I asked myself if there was any other way to do this without adding a column and then deleting it later in the code. I'm sure it wouldn't add any perceptible execution time to do it this way, but I can't stand not having my code be as efficient as possible.

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: Copy/Paste Based on Character Length

    It's not your code, it's what you're trying to do that won't work.

    Regards

  5. #5
    Registered User
    Join Date
    01-12-2011
    Location
    Michigan, USA
    MS-Off Ver
    2010
    Posts
    16

    Re: Copy/Paste Based on Character Length

    Blast the limitations of VBA! Well I suppose that solves it for me then, thanks for the help.

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: Copy/Paste Based on Character Length

    It's not VBA. It's just something you can't do with Autofilter, AFAIK.

    Regards

  7. #7
    Registered User
    Join Date
    01-12-2011
    Location
    Michigan, USA
    MS-Off Ver
    2010
    Posts
    16

    Re: Copy/Paste Based on Character Length

    So then is there a better way to do it without having to create an unnecessary column to delete it later?

  8. #8
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: Copy/Paste Based on Character Length

    If I knew another way to do it without an "unnecessary" helper column, I probably would have suggested that. What does that tell you?

    You could repost and seek a second opinion. I am by no means the most knowledgeable person on the forum. Good luck.

    Regards

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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