Macro to copy cells due to certain cell value and past to bottom of rows on a new sheet

[TomDrury89]

Hi,

I am having difficulty with a copy and paste macro.

I currently have a workbook in which I have a "Master" Sheet and a "ODBC" Sheet.

I am wanting a macro to look at cell values on the ODBC sheet in column T and if they read "New Starter" I am wanting it to copy the aligning cells from C:F and copy to the "Master" sheet at the bottom of the table.

Please help

Thankyou

 

[footoo]

Sub Copy_Paste()
With Sheets("ODBC")
    .Rows(1).Insert
    .[T:T].AutoFilter Field:=1, Criteria1:="New Starter"
    .Range(.[C2], .Cells(Rows.Count, "T").End(xlUp)(1, -16)).Resize(, 4).SpecialCells(xlCellTypeVisible).Copy _
            Sheets("Master").Cells(Rows.Count, "C").End(xlUp)(2)
    .Rows(1).Delete
End With
End Sub

 

[TomDrury89]

Sub Copy_Paste()
With Sheets("ODBC")
    .Rows(1).Insert
    .[T:T].AutoFilter Field:=1, Criteria1:="New Starter"
    .Range(.[C2], .Cells(Rows.Count, "T").End(xlUp)(1, -16)).Resize(, 4).SpecialCells(xlCellTypeVisible).Copy _
            Sheets("Master").Cells(Rows.Count, "C").End(xlUp)(2)
    .Rows(1).Delete
End With
End Sub

Hi Thankyou,

Unfortunately the above did not work. I am getting a Run Time Error 1004 - AutoFilter method of range class failed.

 

[footoo]

I can't reproduce the error.
Do you have any filters on sheet ODBC ?
Do any cells in column T contain "New Starter" ?

 

[KennyGreens]

Hi there, try this:

Sub copy_paste()

Dim master as Worksheet
Dim odbc as Worksheet

Set master = ThisWorkbook.Sheets(“Master”)
Set odbc = ThisWorkbook.Sheets(“ODBC”)

Dim i as Integer
Dim findStr as String
Dim lrOdbc as Long
Dim lrMaster as Long

findStr = “New Starter”
lrOdbc = odbc.Cells(Rows.Count, “T”).End(xlUp).Row

For i = 1 to lrOdbc
lrMaster = master.Cells(Rows.Count, “A”).End(xlUp).Row + 1
If InStr(odbc.Cells(i, “T”), findStr) > 0 Then
odbc.Range(“C” & i & “:F” & i).Copy master.Cells(lrMaster, “A”)
End If
Next

End Sub

 

[TomDrury89]

I can't reproduce the error.
Do you have any filters on sheet ODBC ?
Do any cells in column T contain "New Starter" ?

The "New Starter" is being pulled from a V lookup? Would that cause the issue?

 

[footoo]

The "New Starter" is being pulled from a V lookup? Would that cause the issue?

That shouldn't affect it

You could try the following, but it shouldn't make any difference. Perhaps someone else has some idea.
If your data is not large, you could use KennyGreens' macro.

Sub Copy_Paste()
Dim f As Range
Sheets("ODBC").Activate
Set f = [T:T].Find(what:="New Starter", LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
With ActiveSheet
    .Rows(1).Insert
    .[T:T].AutoFilter Field:=1, Criteria1:="New Starter"
    .Range(.[C2], .Cells(Rows.Count, "T").End(xlUp)(1, -16)).Resize(, 4).SpecialCells(xlCellTypeVisible).Copy _
            Sheets("Master").Cells(Rows.Count, "C").End(xlUp)(2)
    .Rows(1).Delete
End With
End If
End Sub