finding and selecting and copying cells on other sheets

[Randy Reese]

I am using my first sheet to hold command buttons,from sheet1 I want to
select on sheet2 all the rows with "237" in B column but only columns D,E
and F. and copy them to a sheet named "237" , columns a,b,c, after last row.
If this is not clear enough let me know.

 

[Ron de Bruin]

Try this one Randy

The first row is the header row on sheet2
Change your range

Sub Copy_With_AutoFilter()
Dim WS As Worksheet
Dim WS2 As Worksheet
Dim Str As String
Dim LRow As Long

Set WS = Sheets("sheet2")
Set WS2 = Sheets("237")
WS2.Range("a1").Value = "Header"
LRow = LastRow(WS2)

Str = "237"

With WS.Range("b1:F100")
.AutoFilter Field:=1, Criteria1:=Str
WS.Range("D2:F100").Cells.SpecialCells(xlCellTypeVisible).Copy _
WS2.Range("A" & LRow + 1)
End With

WS.AutoFilterMode = False
End Sub


Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function

 

[Ron de Bruin]

You don't need this line Randy
Delete it

WS2.Range("a1").Value = "Header"

 

[Randy Reese]

This code does not give an error but also does not copy.

 

[Ron de Bruin]

Hi Randy

Is working for me?

Send me your workbook (Private) then I take a look at it