Select data based on criteria in VBA

[Dark0Prince]

Right now the code I have takes the data I've selected and moves it to a new sheet then I have to save, it's a great code. Doing this around 300 times seems like there should be a better way. I'm always selecting my data based on the number in column A so if there are a list of different numbers some of them are alike. I would select from the last alike number all the way over to the column J and up to J1 and copy it. Here is the code I'm currently using.

Sub VOPSRELATIVE()
'
' VOPSRELATIVE Macro
'
' Keyboard Shortcut: Ctrl+Shift+R
'
    Selection.Copy
    Workbooks.Add
    ActiveSheet.Paste
    Columns("A:L").Select
    Range("A2").Activate
    Columns("A:L").EntireColumn.AutoFit
    Columns("A:A").Select
    Range("A2").Activate
    Selection.ColumnWidth = 10.5
    Range("F14").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("A7").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveWorkbook.Password = "SECRET"
    Columns(6).EntireColumn.Delete
       Range("A7:K7").Select
    Selection.Copy
End Sub

 

[Dark0Prince]

Re: How can i select data based on criteria in VBA

Ok I fixed the sorting problem it, you weren't seeing alike numbers because the code was sorting column B so it made Column A go out of order. I changed code to this:

    ActiveSheet.Cells(13, 1).CurrentRegion.Sort ActiveSheet.Cells(14, 1), , , , , , , xlYes
    Number = ActiveSheet.Cells(14, 1)
    lastrow = 14
    For Each c In Range(Cells(14, 1), Cells(Cells(Rows.Count, 1).End(xlUp).Row, 1))
        If Number <> c Then Exit For
        lastrow = lastrow + 1
    Next
    Range("A1:J" & lastrow).Copy

New Problem is it isn't only grabbing those client numbers with same number it's also grabbing an extra row.

 

[mart37]

Re: How can i select data based on criteria in VBA

lastrow=13

 

[Dark0Prince]

Re: How can i select data based on criteria in VBA

Perfect! is there a way I can delete those clients after they are copied in the code? Every time I'd try it nothing would paste.

 

[mart37]

Re: How can i select data based on criteria in VBA

    If lastrow = 1[COLOR=#ff0000]3[/COLOR] Then Exit Sub
    Range("A1:J" & lastrow).Copy
    Workbooks.Add
    ActiveSheet.Paste
[COLOR=#ff0000]    Workbooks("EXAMPLE TEST2.xlsm").Sheets(1).Rows("14:" & lastrow).EntireRow.Delete[/COLOR]
    Range("A2").Activate

 

[Dark0Prince]

Re: How can i select data based on criteria in VBA

Thanks a ton!

 

[Dark0Prince]

Re: How can i select data based on criteria in VBA

    If lastrow = 1[COLOR=#ff0000]3[/COLOR] Then Exit Sub
    Range("A1:J" & lastrow).Copy
    Workbooks.Add
    ActiveSheet.Paste
[COLOR=#ff0000]    Workbooks("EXAMPLE TEST2.xlsm").Sheets(1).Rows("14:" & lastrow).EntireRow.Delete[/COLOR]
    Range("A2").Activate

I tried to remove "CurrentRegion.Sort" because I don't want it to sort my first column. I want it to just use the order I already have there. How can I do this properly?

 

[mart37]

Re: How can i select data based on criteria in VBA

delete: ActiveSheet.Cells(13, 1).CurrentRegion.Sort ActiveSheet.Cells(14, 1), , , , , , , xlYes