Trouble with Copy/Paste Cells with a Certain String Name in Them, VBA

Question

I am using this code to check each row in worksheet "Report2" for the phrase "Chicago" and copy and paste any rows with "Chicago" in them into a new sheet. However, it is not working. Any help on why would be appreciated.

Sub BranchCount()

Dim s As Worksheet
Dim LastRow As Long

Set s = Worksheets("Report 1")
LastRow = s.UsedRange.SpecialCells(xlCellTypeLastCell).Row

Worksheets("Report 1").Select
Range("A1:J" & LastRow).Select
Selection.Copy

Sheets.Add.Name = "Report2"
Selection.PasteSpecial xlPasteValues
Range("A1").EntireRow.Delete
Range("B1").EntireRow.Delete
Range("C1").EntireRow.Delete

Dim Z As Range
Dim Y As String

Y = "Chicago"

Dim Q As Worksheet
Dim LastRow2 As Long

Set Q = Worksheets("Report2")
LastRow2 = Q.UsedRange.SpecialCells(xlCellTypeLastCell).Row

Sheets("Report2").Range("A1").Select

For Each Z In Range("J1:J" & LastRow2)
    If Y = Z.Value Then
        Z.EntireRow.Copy
            Sheets("Clean").Select
                Range("A500").End(xlUp).Offset(1, 0).Select
                Selection.PasteSpecial xlPasteValues
            Sheets("Report2").Select
    End If
Next

End Sub

I use Excel 2013. Please let me know if you can help. Thanks!

Do you have an error somewhere? If so, where and what error? How isn't it working? Also, I suggest studying [How to avoid `.Select`](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) as using that can cause headaches/unexpected behavior. — BruceWayne, Apr 08 '16 at 14:20
No error. It is creating the "Report2" worksheet with all of the necessary data, but it doesn't copy and paste any of the rows with "Chicago" in them into the "Clean" worksheet — bbran, Apr 08 '16 at 14:35
@bbran can you please say Chicago is partial match or full match in J column. for ex: J2 contains **Chicago** and J3 contains **Chicago is the largest city**. You want both of them to copy and paste in to Clean worksheet? — Karthick Gunasekaran, Apr 08 '16 at 15:19
Chicago will always be a full match. Column J has a bunch of different cities. So J1 could contain "Chicago", J2 could contain "NYC", J3 could contain "Denver" and J4 could contain "Chicago" again. It will always just be "Chicago" when it pops up in column J — bbran, Apr 08 '16 at 15:27

score 0 · Answer 1 · answered Apr 08 '16 at 14:26

0

I tried some cleaning up, using worksheet variables. You started using them, but should implement them everywhere (again, see the link in my comment).

I think I understood where your data was, but you may need to tweak the below (use F8 to step through one line at a time):

Sub BranchCount()
Dim rptOneWS As Worksheet, rptTwoWS As Worksheet
Dim s As Worksheet
Dim LastRow As Long

Set rptOneWS = Worksheets("Report 1")
Set rptTwoWS = Sheets.Add
rptTwoWS.Name = "Report 2"

LastRow = rptOneWS.UsedRange.SpecialCells(xlCellTypeLastCell).Row

'If you just want values, set two ranges equal. That way you
' skip using the clipboard
rptTwoWS.Range("A1:J" & LastRow).Value = rptOneWS.Range("A1:J" & LastRow).Value
With rptTwoWS
    .Range(.Rows(1), .Rows(3)).EntireRow.Delete
End With

Dim Z As Range
Dim Y As String

Y = "Chicago"

Dim LastRow2 As Long

LastRow2 = rptTwoWS.UsedRange.SpecialCells(xlCellTypeLastCell).Row

For Each Z In rptTwoWS.Range("J1:J" & LastRow2)
    If Y = Z.Value Then
        Z.EntireRow.Copy
            Sheets("Clean").Range("A500").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
    End If
Next

End Sub

answered Apr 08 '16 at 14:26

BruceWayne

22,923
15
65
110

appreciate the effort Bruce, for some reason it still doesn't copy the rows with "Chicago" in them into my "Clean" worksheet – bbran Apr 08 '16 at 14:37
@bbran - Ah, try doing `If z.Value = Y Then ` Also, make sure that yor "Report 2" sheet does have "Chicago", and ***only "Chicago"*** in the J column. (It's supposed to be "Report2" sheet, yeah?) – BruceWayne Apr 08 '16 at 15:09
Still not copying and pasting for some reason – bbran Apr 08 '16 at 15:25
@bbran - Does the code at least copy the entire row? Or does it never find a true value for `If Y = Z.Value`? – BruceWayne Apr 08 '16 at 16:31
it appears to copy 1 row with Chicago into the new sheet, and then each time it comes across a new row with Chicago in it, it replaces the old row in the new sheet – bbran Apr 08 '16 at 19:34

Trouble with Copy/Paste Cells with a Certain String Name in Them, VBA

1 Answers1