Results 1 to 7 of 7

Thread: copy and paste data with seected word in row H
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Feb 2018
    Location
    Midlands, UK
    Posts
    916
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default copy and paste data with seected word in row H

    Hi, good afternoon, just wondering, I have the code below which at the moment copies all data from one sheet to another, but is there any way to collate data which shows a specific word in rows H? for example the word Issues'? so if issues show in row H can it copy all the data in that range and down?

    Code:
    Private Sub CommandButton6_Click()
    Dim x As Workbook
    Dim y As Workbook
    
    Set x = ThisWorkbook
    Set y = Workbooks.Open("G:ISSUES test DO NOT USE.xlsx")
    
    With x.Sheets("Handover")
       .Range("H5:Q" & .Range("H:Q").Find("*", , xlValues, , xlByRows, xlPrevious).Row).Copy y.Sheets("Issued").Range("A2")
    End With
    
    End Sub

  2. #2
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,794
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: copy and paste data with seected word in row H

    is there any way to collate data which shows a specific word in rows H? for example the word Issues'?
    Why not incorporate the use of Filters into your code?
    You can filter on column H for entries that contain the word "Issues".
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    Board Regular
    Join Date
    Feb 2018
    Location
    Midlands, UK
    Posts
    916
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: copy and paste data with seected word in row H

    Hi thanks that sounds a great idea but how would I do that please?

  4. #4
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,794
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: copy and paste data with seected word in row H

    If you use the Macro Recorder, and record yourself performing the steps manually, you will get much of the VBA code that you need.
    We can then help you to clean up the code, or make it more dynamic, if necessary.
    The Macro Recorder is a great tool to get snippets of VBA code (without having to write it).
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  5. #5
    Board Regular
    Join Date
    Feb 2018
    Location
    Midlands, UK
    Posts
    916
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: copy and paste data with seected word in row H

    Ok thank you i shall try this

  6. #6
    Board Regular
    Join Date
    Feb 2018
    Location
    Midlands, UK
    Posts
    916
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: copy and paste data with seected word in row H

    Hi I tried to find the macro recorder but was unable to find in excel, I have put a code together a macro which I have linked to Button23 but this doesn't work, nothing happens, would you please be able to have a look for me? What I am trying to do is filter on the word 'NCMO' in Row 'R' and sheet("Handover") and with that whole row/s copy the data from H5 to Q and paste into sheet ("Issues") rows A2 to J. Hope you can help please.
    Code:
    Public Sub Button23()
        Dim lngLastRow As Long
        Dim lngRow As Long
        Dim strValue As String
        Dim lngRowOutput As Long
     
        lngLastRow = Handover.UsedRange.Rows.Count
     
        If lngLastRow = 1 Then Exit Sub
     
       
        Issues.Range("1:1000").Clear
     
        lngRowOutput = 1
     
        For lngRow = 1 To lngLastRow
            strValue = Handover.Cells(lngRow, 2).Value
     
            If InStr(1, strValue, "NCMO", vbTextCompare) > 0 Then
                Handover.Rows(lngRow).Copy
                Issues.Rows(lngRowOutput).PasteSpecial
                lngRowOutput = lngRowOutput + 1
            End If
     
        Next lngRow
     
     
     
        Application.WindowState = xlNormal
        Range("R1").Select
        Sheets("Issues").Select
        Range("A2:J").Select
        ActiveCell.FormulaR1C1 = ""
        Range("A2:J").Select
        Selection.ClearContents
        Range("A2").Select
        Sheets("Handover").Select
        Application.WindowState = xlNormal
        Sheets("Handover").Select
        ActiveSheet.Shapes.Range(Array("Button 23")).Select
        Selection.OnAction = "Button23"
        Range("R1").Select
    End Sub
    Last edited by Patriot2879; Nov 8th, 2018 at 04:34 AM. Reason: missed something

  7. #7
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,794
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: copy and paste data with seected word in row H

    Hi I tried to find the macro recorder but was unable to find in excel,
    In is under the Data menu, on the Sort & Filter ribbon. There is Filter and Advanced Filter.
    There are many tutorials out there that show you how to use them, if you are not familiar with them (can quickly be found using a Google search).

    I have put a code together a macro which I have linked to Button23 but this doesn't work, nothing happens, would you please be able to have a look for me?
    Have you tried stepping through your code line-by-line to see what is happening? The issue often becomes evident when you can see what is happening each step along the way.
    If you have two monitors, place the worksheet on one, and the VB code on the other, so that you can watch what is happening on your sheet as your step through your code one line at a time using the F8 key.
    If you do not have two monitors, then either split your monitor, or shrink down the VB Editor window and overlay that over your worksheet. The key is to see both at the same time.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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