copy and paste data with seected word in row H

[Patriot2879]

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?

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

 

[Joe4]

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".

 

[Patriot2879]

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

 

[Joe4]

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).

 

[Patriot2879]

Ok thank you i shall try this

 

[Patriot2879]

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.

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

 

[Joe4]

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.