+ Reply to Thread
Results 1 to 6 of 6

Copy row to another sheet if cell equals value

  1. #1
    Registered User
    Join Date
    03-29-2013
    Location
    Houston, Texas
    MS-Off Ver
    Excel 2010
    Posts
    16

    Copy row to another sheet if cell equals value

    I have a workbook with 15 different sheets. The one has a compilation of all of the data (Sheet "SM1") and the rest need the data moved to them from SM1 based on values from SM1, column D. Column D is a a four digit numerical value (for instance the first value in cell D4 is 0111). The rest of the sheets are the numerical values (so sheet 2 is named 0111). I need a marco that will search column D in sheet SM1 for a value, and if a cell in the column matches the value then it moves the entire row to the first empty row in another sheet. I am trying to make this as clear as possible so at the risk of possibly being redundant let me give this example. In sheet "SM1" in cell D4 the value is 0111. I need the macro to search column D, find that D4 matches 0111, cuts the entire row that D4 is in, and moves it to the first empty row in sheet 0111. I think that I know enough about macro's to be able to modify it for the other 13 values that I need. Any help would be greatly appreciated. Thank you.

  2. #2
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Copy row to another sheet if cell equals value

    Hi Aggy,

    This is interactive but we could get the value off a range on the sheet:

    Please Login or Register  to view this content.
    Directions for running the routine(s) just supplied

    Copy the code to the clipboard

    Press ALT + F11 to open the Visual Basic Editor.

    Open a macro-enabled Workbook or save your Workbook As Macro-Enabled

    Select “Module” from the Insert menu

    Type "Option Explicit" then paste the code under it

    With the cursor between Sub and End Sub press F5 (F8 to Single Step)

    OR

    Press ALT + Q to close the code window.

    Press ALT + F8 then double click on the macro name
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  3. #3
    Registered User
    Join Date
    03-29-2013
    Location
    Houston, Texas
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Copy row to another sheet if cell equals value

    xladept,
    Thank you for the quick response. I followed your instructions, however when I ran the macro to test it the following happened:
    A box came up which said "Type Your 4-digit Value Here". I typed in "0111" which is the first 4-digit value I need to search for to be copied over, and recieved the following error message: "Run-time error '9': Subscript out of range"
    When I attempted to debug the script it took me to the third line of code and highlighted the following portion: "Set wo = ActiveWorkbook.Sheets(CStr(V))" any ideas where to go from here?

    Also, and I am not sure this is going to change anything from the standpoint of the macro, I was told by my partner that we need to be able to copy the row with the matching cell to a sheet which does not have the same name as the value in the cell. In my example I stated that the value I was seacrhing for (0111 in the example) would be the same name as the sheet I would be copying to (Sheet "0111") but I now understand that I need to be able to take a value such as 0111 and put it in a sheet titled something different, such as Group 1, Group 2, etc.

    Again, thank you for your help so far

  4. #4
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Copy row to another sheet if cell equals value

    The subscript error is thrown because the sheet is not named "0111" if you can determine the sheet name from the number then:

    Please Login or Register  to view this content.
    Put that name in place of the reddened code
    Last edited by xladept; 03-30-2013 at 05:21 PM.

  5. #5
    Registered User
    Join Date
    03-29-2013
    Location
    Houston, Texas
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Copy row to another sheet if cell equals value

    Thank you so much, I was able to make it work. Your help is greatly appreciated

  6. #6
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Copy row to another sheet if cell equals value

    You're welcome!

+ 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