VBA Excel cut/paste multiple emails into another worksheet

Question

I am trying to move specific data from one sheet to another with a loop. I need the macro to look emails and then cut all the data for those rows and paste them into the next tab. I have the below code, but my .Value = "@" isn't recognized. If I do .Value = "" Then it recognizes all the blank cells. Help please, I know it's probably something super simple.

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

For i = 2 To a

    If Worksheets("Master").Cells(i, 12).Value = "@" Then
        Worksheets("Master").Rows(i).Cut
        Worksheets("Email").Activate
        b = Worksheets("Email").Cells(Rows.Count, 1).End(xlUp).Row
        Worksheets("Email").Cells(b + 1, 1).Select
        ActiveSheet.Paste
        Worksheets("Master").Activate

    End If

Next

Application.CutCopyMode = False

ThisWorkbook.Worksheets("Master").Cells(1,1).Select


End Sub

Jchang43 Jchang43 · Accepted Answer · 2018-10-18T23:24:50

Alright so the reason this isn't working is because you're asking whether or not the value of Cells(i,12) is equal to @, but I'm assuming you're asking if it contains the character @. The way you do this is by using like instead of = and adding wildcards (*)to the string you're comparing. So what wildcards do is they say anything can be on the other side of the specified characters and it'll count as a match. For example @* would match with anything starting with @ and *@ would match with anything ending in @. By combining them you get *@* which matches with anything containing @.

It's also good to note that you could collapse most of this into one line by working directly with objects instead of activating them.

You should also get in the habit of using Option Explicit and Dimming your variables it prevents you from using uninitialized variables which could help prevent errors in longer pieces of code.

I've added a bit of code at the end that deletes all of the blank cells in the used range of the master worksheet. This will shift everything up, so unless everything you care about is in blocks with no empty cells in between, I would comment it out. It does, however, help condense down the master list if you choose to use it.

Option Explicit

Private Sub CommandButton1_Click()

Dim A as Long
Dim i as Long

A = Worksheets("Master").Cells(Rows.Count, 1).End(xlUp).Row
Application.CutCopyMode = False

For i = 2 To A

    If Worksheets("Master").Cells(i, 12).Value Like "*@*" Then
        Worksheets("Master").Rows(i).Copy Destination:= Worksheets("Email").Cells(Worksheets("Email").Cells(Rows.Count, 1).End(xlUp).Row + 1, 1)
        Worksheets("Master").Rows(i).Clear
    End If

Next

Worksheets("Master").UsedRange.SpecialCells(xlCelltypeBlanks).Delete shift:=xlShiftUp

Application.CutCopyMode = True
End Sub

VBA Excel cut/paste multiple emails into another worksheet

1 Answers