How to copy data from cells with specific value to another worksheet?

user_16068906 0
Viewed 33

I am not very familiar with VBA and I need help with programming a code to do the following:

On Button Click in Sheet 1

  1. Copy values from Column A if value =1 to worksheet 2 into column A.
  2. If the value = 2 then copy it to worksheet 3 into column A.

This is my current code.

Sub Mandat1_Click()
    For Each Cell In Range("A2:A81")
        If Cell.Value = 1 Then
            Sheets(3).Range("C2:C81").Value = Sheets(1).Range("A2:A81").Value
        End If
    Next Cell
End Sub
vba excel
2 Answers

This is my best guess at what you are trying to do.

Sub moveData()

    Dim ws1 As Worksheet: Set ws1 = ThisWorkbook.Worksheets(1)
    Dim ws2 As Worksheet: Set ws2 = ThisWorkbook.Worksheets(2)
    Dim ws3 As Worksheet: Set ws3 = ThisWorkbook.Worksheets(3)
    
    Dim r As Integer
    For r = 2 To 81
        If ws1.Cells(r, 1).Value = 1 Then
            ws2.Cells(r, 1).Value = ws1.Cells(r, 1).Value
        ElseIf ws1.Cells(r, 1).Value = 2 Then
            ws3.Cells(r, 1).Value = ws1.Cells(r, 1).Value
        End If
    Next r

End Sub
user_148805780 
Sub TransferData()


Dim lastrow As Long, erow As Long

'Check last filled row
lastrow = Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To lastrow

If Sheets(1).Cells(i, 1).Value = 1 Then


'Copies data from from Sheet 1 column 1
Sheets(1).Cells(i, 1).Copy

erow = Sheets(2).Cells(Rows.Count, 1).End(xlUp).Row

'Pastes data in Sheet 2 column 1
Sheets(1).Paste Destination:=Sheets(2).Cells(erow + 1, 1)


Sheets(1).Cells(i, 1).Copy

eerow = Sheets(6).Cells(Rows.Count, 1).End(xlUp).Row

Sheets(1).Paste Destination:=Sheets(6).Cells(eerow + 1, 1)

End If

Next i

End Sub
user_160689060
Related