Excel VBA Copy row to another sheet based on cell value

[flyfishermanmike]

I have basic Excel knowledge and very little VBA. I have a spreadsheet where I keep all my reloading data. I keep all this data in a master sheet and add new loads to this sheet as needed. I reload multiple calibers and would like to sort each caliber to its own specific sheet with a macro rather than sorting then copying and pasting each time I add new data. For instance I'd like to find all occurrences of 9mm in column C, copy that row and then paste it into a 9mm sheet. Sounds simple enough but I'm struggling. Hopefully this makes sense from my sample below.

https://www.dropbox.com/s/zkj5aeg0np2gfil/My Reloading Data With Macros - Sample.xlsm?dl=0

Also, each load I enter gets a unique load number that is calculated automatically with a formula, based on the caliber name and a incrementally increasing number. As of now, every load I enter gets a number, even if it's been repeated before. Popular loads that I repeat often are all the same except for the date and numbers of rounds made but will have different load numbers. Is there a way to skips these repeated loads and assign it the previous load number or not assign a load number at all, with code instead of manually?

So far, if I need to sort I've been copying the values to another sheet, otherwise the formula will then change the load numbers. Is there an easy way to keep those load numbers fixed even during sorting? My formula probably isn't the best way to accomplish this but has worked so far.

I know this is asking for a great deal but I'd greatly appreciate any help!

^^ike

 

[Ramballah]

I'm not quite sure how much you know about VBA cos im not the best in it my self. If you have already a small code written i could adjust it probably. But im not sure if i can make the entire macro even though it aint a big one as i have something similar in my own workbook.

 

[flyfishermanmike]

I'm not quite sure how much you know about VBA cos im not the best in it my self. If you have already a small code written i could adjust it probably. But im not sure if i can make the entire macro even though it aint a big one as i have something similar in my own workbook.

Here's what I've been playing with, found elsewhere for a similar situation. It mostly works but doesn't copy the data to the last used row in the other sheet.

Sub MoveRowBasedOnCellValueTEST()

End Sub
    Dim xRg As Range
    Dim xCell As Range
    Dim I As Long
    Dim J As Long
    Dim K As Long
    I = Worksheets("Reloading Data").UsedRange.Rows.Count
    J = Worksheets("9mm").UsedRange.Rows.Count
    If J = 1 Then
    If Application.WorksheetFunction.CountA(Worksheets("9mm").UsedRange) = 0 Then J = 0
    End If
    Set xRg = Worksheets("Reloading Data").Range("C1:C" & I)
    On Error Resume Next
    Application.ScreenUpdating = False
    For K = 1 To xRg.Count
        If CStr(xRg(K).Value) = "9mm" Then
            xRg(K).EntireRow.Copy Destination:=Worksheets("9mm").Range("A" & J + 1)
            J = J + 1
        End If
    Next
    Application.ScreenUpdating = True
    
End Sub

 

[flyfishermanmike]

This seems to work better:

Sub MoveRowBasedOnCellValueTEST()

    Dim xRg As Range
    Dim xCell As Range
    Dim I As Long
    Dim J As Long
    Dim K As Long
    I = Worksheets("Reloading Data").UsedRange.Rows.Count
    J = Worksheets("9mm").UsedRange.Rows.Count
    If J = 1 Then
    If Application.WorksheetFunction.CountA(Worksheets("9mm").UsedRange) = 0 Then J = 0
    End If
    Set xRg = Worksheets("Reloading Data").Range("C1:C" & I)
    On Error Resume Next
    Application.ScreenUpdating = False
    For K = 1 To xRg.Count
        If CStr(xRg(K).Value) = "9mm" Then
            xRg(K).EntireRow.Copy Destination:=Worksheets("9mm").Range("A" & Rows.Count).End(xlUp).Offset(1)
        End If
    Next
    Application.ScreenUpdating = True
    
End Sub