PDA

View Full Version : Copy Rows - If it Contains a Word Listed in Column A Data Table



dj44
09-17-2018, 02:30 AM
Good morning and monday folks,



I have lots of rows to copy and autofilter 1 by one is too slow

i need some efficieny to prevail now.

I am trying to copy rows from 1 worksheet to another

I have set up the basic idea

But I am not able to workout how to do it after the nth attempt


If any one has any time please can you take a look at the workbook attached

very gratefully appreciated as always

nikki333
09-22-2018, 07:09 AM
Hi there

Try this in your module:


Sub Copy_Rows()

Dim i As Long
Dim ws As Worksheet
Dim oDest As Worksheet
Dim oData As Worksheet
Dim strRange As String

Set ws = Worksheets("NamesList") ' Rows I want to copy listed here
Set oData = Worksheets("Data") ' This Contains the Rows to Copy
' Set oDest = Worksheets("Sheet2") ' >>> Wrong Sheet name
Set oDest = Worksheets("Sheet 2") ' Paste here

For i = 2 To ws.Cells(ws.Rows.Count, "A").End(xlUp).Row ' Cells to Copy IFinstring

' ws.Range(Cells(i, "A")).Copy Destination:=oDest.Range(ws.Cells(i, "B").Value) '>>> Wrong cell reference
ws.Cells(i, "A").Copy Destination:=oDest.Range(ws.Cells(i, "B"))

Next i

End Sub