copy and paste data with seected word in row H

Patriot2879

Well-known Member
Joined
Feb 1, 2018
Messages
1,227
Office Version
  1. 2010
Platform
  1. Windows
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
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
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".
 
Upvote 0
Hi thanks that sounds a great idea but how would I do that please?
 
Upvote 0
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).
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,941
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top