Copy and Paste Criteria

[jmwinkler]

I am pretty new to VBA coding and I am completely stuck on a piece of code. I found some code on this forum site and started to manipulate it to try and fit my project. I would like to copy and paste a row of data based on a condition. I want to start at row 3 and copy from Cells A to Q if cell E is less than 10500 for the worksheet "START-Combine Drops and Station" and paste the values into the worksheet "9000 lb drops only" starting in row 3. The code results in nothing being copied over and the cells I am copying are all formulas which I think that might be where Im getting hung up. Any help would be much appreciated. Thank you.

Option Explicit


Sub Copy_n_Paste()
On Error Resume Next


    Dim srchtrm As String
    Dim rng As Range, destRow As Long
    Dim shtSrc As Worksheet, shtDest As Worksheet
    Dim c As Range
    Dim i As Integer
    Dim Today As Date
    
    With Application
        .ScreenUpdating = False
        .DisplayStatusBar = False
        .Calculation = xlCalculationManual
        .EnableEvents = False
    End With
    
    Set shtSrc = Sheets("START-Combine Drops and Station")
    Set shtDest = Sheets("9000 lb drops only") 
    destRow = 3


    'don't scan the entire column...
    Set rng = Application.Intersect(shtSrc.Range("A:Q"), shtSrc.UsedRange)


    For Each c In rng.Cells
        If c.Value < 10250 Then
            
            c.EntireRow.Copy.Values shtDest.Cells(destRow, 1)
          
            destRow = destRow + 1


        End If
    Next
    
    With Application
        .ScreenUpdating = True
        .DisplayStatusBar = True
        .Calculation = xlCalculationAutomatic
        .EnableEvents = True
    End With
        
    Application.CutCopyMode = False
    Sheets("START-Combine Drops and Station").Range("A1").Select
End Sub

[/CODE]

 

[vcoolio]

Hello jmwinkler,

You could simply filter on Column E for values less than 10500 as follows:-

Sub Test()

        Dim shtSrc As Worksheet: Set shtSrc = Sheets("START-Combine Drops and Station")
        Dim shtDest As Worksheet: Set shtDest = Sheets("9000 lb drops only")

Application.ScreenUpdating = False

        With shtSrc.Range("E2", shtSrc.Range("E" & shtSrc.Rows.Count).End(xlUp))
                .AutoFilter 1, "<" & 10500
                           .Columns("A:Q").Offset(1, -4).Copy
                           shtDest.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues
                .AutoFilter
        End With

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

With the code, I'm assuming that your headings are in row2 with data starting in row3.

I hope that this helps.

Cheerio,
vcoolio.

 

[jmwinkler]

Hey vcoolio,

That was way simpler, than the way I was doing it and worked like a charm. Thanks!

 

[jmwinkler]

Hello jmwinkler,

You could simply filter on Column E for values less than 10500 as follows:-

Sub Test()

        Dim shtSrc As Worksheet: Set shtSrc = Sheets("START-Combine Drops and Station")
        Dim shtDest As Worksheet: Set shtDest = Sheets("9000 lb drops only")

Application.ScreenUpdating = False

        With shtSrc.Range("E2", shtSrc.Range("E" & shtSrc.Rows.Count).End(xlUp))
                .AutoFilter 1, "<" & 10500
                           .Columns("A:Q").Offset(1, -4).Copy
                           shtDest.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues
                .AutoFilter
        End With

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

With the code, I'm assuming that your headings are in row2 with data starting in row3.

I hope that this helps.

Cheerio,
vcoolio.

Hey vcoolio,

If I wanted to now sort between a range of greater than 10500 and less than 13000, how would I do that?

 

[vcoolio]

Hello JMWinkler,

You're welcome. I'm glad to have been able to assist.

With your second query, I'm assuming that you actually mean filter between those values. If so, try this:-

Sub Test()

        Dim shtSrc As Worksheet: Set shtSrc = Sheets("START-Combine Drops and Station")
        Dim shtDest As Worksheet: Set shtDest = Sheets("9000 lb drops only")

Application.ScreenUpdating = False

        With shtSrc.Range("E2", shtSrc.Range("E" & shtSrc.Rows.Count).End(xlUp))
                .AutoFilter 1, ">" & 10500, xlAnd, "<" & 13000
                           .Columns("A:Q").Offset(1, -4).Copy
                           shtDest.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues
                .AutoFilter
        End With

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

I hope that this helps.

Cheerio,
vcoolio.