Find end of number and copy/paste 5 columns

[taylorkand]

Hi all,

I am having trouble with removing some data before inserting it into a
pivot table. In column g of my excel database, i have shift -trip
numbers in the following format XXXXXXX-X. Where all X's are numbers.
My issue is that I need to find all shift-trip numbers that end in -1
and copy and paste them to a new page, these are the entries I need
for further analysis. Any ideas on how to recognize the end "-1"? I
am not having luck with anything I've seen online so far, though I am
very new to vb.

Thanks in advance,
Kyle

 

[Bernie Deitrick]

Kyle,

Not sure which 5 columns for want, or where you want the cells copied, but code like this will do
it. I assumed that your database sheet is Sheet1, and you are copying to Sheet2 to the bottom of
column A, and you want column G, H, I, J, and K copied.

Dim myR As Range
Set myR = Intersect(Worksheets("Sheet1").Range("G:G"), _
Worksheets("Sheet1").UsedRange)
myR.AutoFilter Field:=1, Criteria1:="=*-1"
myR.Offset(1, 0).Resize(myR.Rows.Count - 1, 5). _
SpecialCells(xlCellTypeVisible).Copy _
Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp)(2)
myR.AutoFilter
Application.CutCopyMode = False


HTH,
Bernie
MS Excel MVP