Results 1 to 12 of 12
  1. #1
    2 Star Lounger
    Join Date
    Feb 2015
    Posts
    128
    Thanks
    19
    Thanked 1 Time in 1 Post

    Post How to move specific data from one sheet to another.

    Hi Experts
    Please help to deal with this tricky situation. I have RAW data in a haphazard condition. I need specific data to be moved from sheet1 to Sheet2. I have created a sample replica of the original data and it is attached with the thread.
    Kindly suggest some remedy for it.
    Regards,
    JD
    Attached Files Attached Files

  2. #2
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    Use a macro to copy to sh1 from sh2. Save file as .xlsm or
    Code:
    Option Explicit
    Sub CopyEm()
    Dim i As Long
    Dim dr As Long
    For i = 15 To Cells(Rows.Count, 1).End(xlUp).Row Step 7
    dr = Sheet2.Cells(Rows.Count, 1).End(xlUp).Row + 1
    With Sheet2
    .Cells(dr, 1).Value = Cells(i, 1).Value
    .Cells(dr, 2).Value = Cells(i, 4).Value
    .Cells(dr, 3).Value = Cells(i + 2, 1).Value
    'preforemat sh2.column D as Date desired
    .Cells(dr, 4).Value = Left(Cells(i + 5, 1), 6)
    .Cells(dr, 5).Value = Cells(i + 1, 1).Value
    .Cells(dr, 6).Value = Left(Cells(i, "s"), 6)
    End With
    Next i
    End Sub
    Last edited by RetiredGeek; 2015-03-24 at 15:26. Reason: Added Code Tags
    Don Guillett
    Excel Developer
    dguillett @gmail.com

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Don

    ..a great piece of code, much better than using formulas for the extract.

    zeddy

  4. #4
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    Appreciate the comment
    Don Guillett
    Excel Developer
    dguillett @gmail.com

  5. #5
    2 Star Lounger
    Join Date
    Feb 2015
    Posts
    128
    Thanks
    19
    Thanked 1 Time in 1 Post
    Hi Don

    Fantastic piece of code!

    I have a small requirement. Say I need to pull a word from the cell "Injury: Strain To: Lower Back Area" in the code

    .Cells(dr, 7).Value =

    I tried adding the formula to get only the word "Strain" from the respective cell, but was not succeeded. Could you please let me know if this is possible in the current set of code.

    Regards,
    JD

  6. #6
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    No such shown in sample ????
    Don Guillett
    Excel Developer
    dguillett @gmail.com

  7. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,641
    Thanks
    115
    Thanked 652 Times in 594 Posts
    Assuming that .cells(1,1) contains- Injury: Strain To: Lower Back Area

    the line of code would be: .Cells(dr, 7).Value = Mid(.Cells(1, 1), 9, 6)

    Maud

  8. #8
    2 Star Lounger
    Join Date
    Feb 2015
    Posts
    128
    Thanks
    19
    Thanked 1 Time in 1 Post
    Hi Don and Maud

    @Don - I am using the same code in one of my other document, where the requirement is little different. I manage to pull most of the data with your code, but got stuck with few of the data and the above one is the example of it.

    @Maud - Thanks for the code, but it only picks the word with 6 letters only. I have words which needs to be picked of different length. Like in Excel the same thing can be done with the formula "=MID(G90,9,FIND(" ",G90,9)-9)"

    Is it possible to implement the sample formula in the above VBA code.

    Data -

    Injury: Laceration To: Finger(s)
    Injury: Strain To: Lower Back Area
    Injury: Contusion To: Multiple Head Injury

    Output:

    Laceration
    Strain
    Contusion

    Regards,
    JD
    Last edited by Jaggi; 2015-03-26 at 09:19. Reason: Modification

  9. #9
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,641
    Thanks
    115
    Thanked 652 Times in 594 Posts
    Jaggi,

    Here is a UDF to extract your term. Use the formula =Term(range) where range is the cell with the term you wish to extract. Copy down if applicable. As long as the structure of the string is consistent, it will find your term. String:<space>Term<space>.....

    HTH,
    Maud

    Term.png

    Code:
    Public Function Term(rng As Range) As String
        Dim s, t
        s = Split(rng, ": ")
        t = Split(s(1), " ")
        Term = t(0)
    End Function
    Attached Files Attached Files
    Last edited by Maudibe; 2015-03-28 at 20:44. Reason: added file

  10. #10
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Maud

    Nice use of split, one of my favourite functions.

    Why not use
    Code:
    Public Function Term(rng As Range) As String
        s = Split(rng, " ")
        Term = s(1)
    End Function
    ..shorter is good?

    zeddy

  11. #11
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,641
    Thanks
    115
    Thanked 652 Times in 594 Posts
    Zeddy,

    Yep, you are correct. I must have had some scenario on my mind that would have required it.

    Maud

  12. #12
    2 Star Lounger
    Join Date
    Feb 2015
    Posts
    128
    Thanks
    19
    Thanked 1 Time in 1 Post
    Hi Zeddy, Maud and Don

    Sorry for the late reply on the above thread. I was away on my annual vacation and returned back today. This perfectly fits in my requirement and solve all my concerns.

    Regards,
    JD

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •