I am trying to copy specific columns in a row to another excel sheet based on it meeting two criteria

Question

Trying to copy specific columns in a row to another excel sheet based on it meeting certain criteria.

Never done this before and have tried to copy what others have done but keeps telling me error.

YouTube tutorials

Private Sub CommandButton1_Click()
    a = Worksheets("Salesman Quotes Active").Cells(Rows.Count, 1).End(xlUp).Row

    For i = 3 To 65
        If Worksheets("Salesman Quotes Active").Cells(i, 10).Value = "Warm 2020" Then Worksheets("Salesman Quotes Active").Range(Cells(i, 1), Cells(i, 2), Cells(i, 8), Cells(i, 10)).Copy
        Worksheets("2020 Monetary vs Date anlalysis").Activate
        b = Worksheets("Salesman Quotes Active").Cells(Rows.Count, 1).End(xlUp).Row
        Worksheets("2020 Monetary vs Date analysis").Cells(b + 1, 1).Select
        ActiveSheet.Paste
        Worksheets("Salesman Quotes Active").Activate
    Next

    Application.CutCopyMode = False
    ThisWorkbook.Worksheets("Salesmans Quotes Active").Cells(1, 1).Select
End Sub

Runtime error "450"

First of all you should read [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) and apply this technique to your code. • Note that if you tell us there is an error we need to know the exact error message and in which line it occurs in order to help you. — Pᴇʜ, Oct 29 '19 at 11:11

Pᴇʜ · Answer 1 · 2019-10-29T11:56:18.777

The first problem is that the Worksheet.Range property can only take 2 parameters but you tried to submit 4:

Worksheets("Salesman Quotes Active").Range(Cells(i, 1), Cells(i, 2), Cells(i, 8), Cells(i, 10)).Copy

So you must either copy cell by cell or use the Application.Union method:

With Worksheets("Salesman Quotes Active")
    Union(.Cells(i, 1), .Cells(i, 2), .Cells(i, 8), .Cells(i, 10)).Copy
End With

The second problem is that your Range() object is specified to be in a specific worksheet Worksheets("Salesman Quotes Active").Range(…) but your Cells() objects are not specified to be in the same worksheet as the Range() object and this might cause an error too.
Always specify in which worksheet a Range or Cells object is.

score 0 · Answer 2 · answered Oct 29 '19 at 11:34

According to what I am understand from your question, try this one:

Private Sub CommandButton1_Click()
Dim i as Long
Dim b as Long
Dim ws1 as WorkSheet
Dim ws2 as WorkSheet
Set ws1 = Worksheets("Salesman Quotes Active")
Set ws2 = Worksheets("2020 Monetary vs Date anlalysis")

For i = 3 To 65
    If ws1.Cells(i, 10).Value = "Warm 2020" Then 
        b = ws1.Cells(Rows.Count, 1).End(xlUp).Row
        ws2.Cells(b + 1, 1).Value = ws1.Cells(i, 1).Value
        ws2.Cells(b + 1, 2).Value = ws1.Cells(i, 2).Value
        ws2.Cells(b + 1, 3).Value = ws1.Cells(i, 8).Value
        ws2.Cells(b + 1, 4).Value = ws1.Cells(i, 10).Value
    End If
Next
Application.CutCopyMode = False
End Sub

If any modification is needed just let me know. Hope it helps!

I am trying to copy specific columns in a row to another excel sheet based on it meeting two criteria

2 Answers2