+ Reply to Thread
Results 1 to 9 of 9

Copying a row from a worksheet to multiple new sheets depending on the word in the column

  1. #1
    Registered User
    Join Date
    03-09-2015
    Location
    Salt Lake City
    MS-Off Ver
    2013
    Posts
    3

    Copying a row from a worksheet to multiple new sheets depending on the word in the column

    I have been successfully able to copy an entire row from one worksheet to another worksheet based upon a value that was in a cell in a particular column of a large worksheet using a VBA code. However, I am needing to take this to the next step by reviewing a column in a data set and sorting the data (entire row) from one sheet to multiple other sheets depending on the entry in the cell in a particular column.

    For example:
    1) I have a starting list that is represented as the "Combined" tab. The example is 10 items, my real list is in the high hundreds and even the thousands.
    2) I have a set of other tabs that need the information separated into the respective tabs by the value in the "D" column titled "Color"
    3) After some sort of VBA macro is run, the data will from the "Combined" d and the entire row will be copied to the respective tab. There is a specific tab for all values in the "Color" column.

    Sort Macro Picture.png

  2. #2
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: Copying a row from a worksheet to multiple new sheets depending on the word in the col

    Can you send the sample file you prepared.
    - Battle without fear gives no glory - Just try

  3. #3
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: Copying a row from a worksheet to multiple new sheets depending on the word in the col

    See attached
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    03-09-2015
    Location
    Salt Lake City
    MS-Off Ver
    2013
    Posts
    3

    Re: Copying a row from a worksheet to multiple new sheets depending on the word in the col

    Hi PCI,

    I downloaded the data file. I do not need to analyze this data. I know how do do pivot tables for that kind of issue. Does the "Quick Analysis" tool copy the row where the data fits to the new sheet. I was not able to see that function and is the challenge I am looking to solve.

    Thanks
    Last edited by smadadach; 03-09-2015 at 06:55 PM.

  5. #5
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Copying a row from a worksheet to multiple new sheets depending on the word in the col

    Quote Originally Posted by smadadach View Post
    I have been successfully able to copy an entire row from one worksheet to another worksheet based upon a value that was in a cell in a particular column of a large worksheet using a VBA code. However, I am needing to take this to the next step by reviewing a column in a data set and sorting the data (entire row) from one sheet to multiple other sheets depending on the entry in the cell in a particular column. .......
    Hi smadadach

    . I have done a code for you and checked it in your File. It appears to do the job for you.
    .
    . It takes for example this

    Excel 2007
    Row\Col
    A
    B
    C
    D
    1
    Reference
    Owner
    Type
    Color
    2
    1
    Mike Soft Red
    3
    2
    Taylor Hard Green
    4
    3
    Mike Hard Blue
    5
    4
    Owen Soft Black
    6
    5
    Peter Hard Red
    7
    6
    Peter Soft Yellow
    8
    7
    Owen Hard Red
    9
    8
    Mike Hard Green
    10
    9
    Taylor Soft Blue
    11
    10
    Owen Soft Black

    Sheet Name “Combined”

    ……


    And return the various Color sheets, for example
    Excel 2007
    Row\Col
    A
    B
    C
    D
    1
    Reference Owner Type Color
    2
    1
    Mike Soft Red
    3
    5
    Peter Hard Red
    4
    7
    Owen Hard Red

    Sheet “Red”

    And

    Excel 2007
    Row\Col
    A
    B
    C
    D
    1
    Reference Owner Type Color
    2
    2
    Taylor Hard Green
    3
    8
    Mike Hard Green
    4
    5

    Sheet “Green”
    ….
    . ….etc

    The basic following code has almost certainly many extra bits, including the many Comment which you may not need. I am sure you can work through and delete those if necessary.

    Full Code:

    Please Login or Register  to view this content.
    . Very Briefly the code makes an Array of unique colors, then uses this to do a Filtered Copy of Data from the Combined sheet into Appropritely named Color sheets.
    . You may wish to take a look here where I developed the Code, and more detailed explanations are given
    http://www.mrexcel.com/forum/excel-q...-column-4.html
    http://www.mrexcel.com/forum/excel-q...e-value-2.html
    . Any Question, get back to me. I have the working File if you need it (XL2007 .xlsm)

    Alan..
    '_- Google first, like this _ site:ExcelForum.com Gamut
    Use Code Tags: Highlight code; click on the # icon above,
    Post screenshots COPYABLE to a Spredsheet; NOT IMAGES PLEASE
    http://www.excelforum.com/the-water-...ml#post4109080
    https://app.box.com/s/gjpa8mk8ko4vkwcke3ig2w8z2wkfvrtv
    http://excelmatters.com/excel-forums/ ( Scrolll down to bottom )

  6. #6
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Copying a row from a worksheet to multiple new sheets depending on the word in the col

    Quote Originally Posted by PCI View Post
    See attached
    Hi PCI.
    . I find Threads where more than one solution is given extremely helpful in my Learning of VBA. I had answered similar Threads to this using methods similar to that I gave in Post #5 here.
    . I posted my solution as a contribution as I am learning currently from studying your code.
    . Thanks for that code.
    Alan

    P.s. If you had the time to write some ‘Comments on that code of yours and Post it here I would be very grateful. And I think it would be a worthwhile contribution to the Thread. But I shall work carefully through you code myself sometime until I understand it fully.
    . ( If you had a version not using the With End With technique I would be grateful to see it. I do just about understand this technique, but I find at my current knowledge level it makes it harder for me to work through and understand. )
    Last edited by Doc.AElstein; 03-10-2015 at 09:20 AM.

  7. #7
    Registered User
    Join Date
    03-09-2015
    Location
    Salt Lake City
    MS-Off Ver
    2013
    Posts
    3

    Re: Copying a row from a worksheet to multiple new sheets depending on the word in the col

    Alan,
    This works fantastic for my purpose. Thank You!
    Smadadach

  8. #8
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: Copying a row from a worksheet to multiple new sheets depending on the word in the col

    Hi Doc.AElstein, I'm please to share.

  9. #9
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Copying a row from a worksheet to multiple new sheets depending on the word in the col

    Quote Originally Posted by smadadach View Post
    Alan,
    This works fantastic for my purpose. Thank You!
    Smadadach
    . Thanks for the Feedback.Note that the PCI code works very similarly, and is probably the more Professional Solution.
    Alan.

    P.s.
    . Your screenshot Images in Post #1 explained your problem very well. Gave a very Clear Picture of what you wanted.
    . In addition posting some Table or some sort of screenshots of ranges that can be copied into a Workbook is very helpful. (The problem with Images is that we cannot copy the data, so have to type your test data in by hand. – I was able to use what PCI had done for you ).
    . Failing that a Worksheet as PCI asked for in Post #2 is an alternative

+ 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. Copying data from multiple workbooks having multiple sheets into one master worksheet
    By Navya Ahuja in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-15-2014, 09:42 AM
  2. Copying from multiple sheets (same column) to a main sheet (column)
    By supernovartis in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-08-2013, 11:30 AM
  3. [SOLVED] Sorting data from a master sheet to multiple sheets depending on values in a given column
    By kodos10 in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 05-30-2013, 05:46 AM
  4. Copying first word to a column with multiple criteria
    By Ghassan1088 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-25-2013, 10:45 PM
  5. Copying all data from multiple tabbed sheets to another worksheet
    By StuntMonkeh in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-08-2008, 03:59 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