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

TomDrury89

New Member
Joined
Aug 22, 2018
Messages
14
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
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Code:
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
 
Upvote 0
Code:
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.
 
Upvote 0
I can't reproduce the error.
Do you have any filters on sheet ODBC ?
Do any cells in column T contain "New Starter" ?
 
Upvote 0
Hi there, try this:

Code:
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
 
Upvote 0
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.
Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

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