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

 

[mart37]

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

To find the row with the last double number you can use:

Sub LastAlikeNumber()
Dim lastrow, y, LastAlikeNumberRow As Long
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
For y = lastrow To 1 Step -1
    If Application.WorksheetFunction.CountIf(Columns(1), Cells(y, 1)) > 1 Then
        LastAlikeNumberRow = y
        y = 0
    End If
Next y
End Sub

 

[Dark0Prince]

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

I don't know how to use this in my code to select the data. That's why I still use either destination= or selection.Copy.

 

[mart37]

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

Sub VOPSRELATIVE()
'
' VOPSRELATIVE Macro
'
' Keyboard Shortcut: Ctrl+Shift+R
'
    Dim lastrow, y, LastAlikeNumberRow As Long
    lastrow = Cells(Rows.Count, 1).End(xlUp).Row
    For y = lastrow To 1 Step -1
        If Application.WorksheetFunction.CountIf(Columns(1), Cells(y, 1)) > 1 Then
            LastAlikeNumberRow = y
            y = 0
        End If
    Next y
    Range("A1:J" & LastAlikeNumberRow).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

This code worked but instead of getting the next alike number it went to the end of my document and copied everything but I have a list of alike numbers for example some are(list is below): So what I want it to do is grab all the 543543 rows, then the next time I press "ctrl-shift-R" I want it to grab all the 434343's. if I could go a step further and have it delete the 543543 rows after it is done then I could just keep pressing the button to make another file.

543543
543543
543543
434343
434343
777777
543543
543543

 

[mart37]

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

It is easy as the data is sort on column A
The macro does that.

Sub VOPSRELATIVE()
'
' VOPSRELATIVE Macro
'
' Keyboard Shortcut: Ctrl+Shift+R
'
    ActiveSheet.Cells(1, 1).CurrentRegion.Sort ActiveSheet.Cells(2, 1), , , , , , , xlYes
    Number = ActiveSheet.Cells(2, 1)
    lastrow = 1
    For Each c In Range(Cells(2, 1), Cells(Cells(Rows.Count, 1).End(xlUp).Row, 1))
        If Number <> c Then Exit For
        lastrow = lastrow + 1
    Next
    If lastrow = 1 Then Exit Sub
    Range("A1:J" & lastrow).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

How do i give an example of my workbook?

 

[Dark0Prince]

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

It is easy as the data is sort on column A
The macro does that.

Sub VOPSRELATIVE()
'
' VOPSRELATIVE Macro
'
' Keyboard Shortcut: Ctrl+Shift+R
'
    ActiveSheet.Cells(1, 1).CurrentRegion.Sort ActiveSheet.Cells(2, 1), , , , , , , xlYes
    Number = ActiveSheet.Cells(2, 1)
    lastrow = 1
    For Each c In Range(Cells(2, 1), Cells(Cells(Rows.Count, 1).End(xlUp).Row, 1))
        If Number <> c Then Exit For
        lastrow = lastrow + 1
    Next
    If lastrow = 1 Then Exit Sub
    Range("A1:J" & lastrow).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

Ok here is my example workbook and this code doesn't seem to work for me. https://1drv.ms/x/s!Ap0t6Fmj7XZeg3DdoehtYacJiX_2

 

[mart37]

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

I don't see alike numbers in column B.

Replace the first lines with:

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

 

[Dark0Prince]

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

I'm not using Column B for selection criteria I said column A. Also when I use this code it's resorting my data for some reason when it should just be using column A to pull the client numbers that are the same and putting them in a new workbook.