0

I want to copy rows from sheet1 only when the cell in column B has a specific value which is a string. I used the code below, which seems to copy the rows, but just pastes empty cells in the new sheet.

I think the problem is that it is looking for a value, not a string (can a string be a value?). Do I need to define the cell as a string? Sorry if these are silly questions - new to coding.

Sub copy_rows()
    Sheets.Add After:=Sheets(1)
    Sheets(2).Name = "New"
    For Each cell In Sheets(1).Range("B:B")
        If cell.Value = "banana" Then
            matchRow = cell.Row
            Rows(matchRow & ":" & matchRow).Select
            Selection.Copy
            Sheets("New").Select
            ActiveSheet.Rows(matchRow).Select
            ActiveSheet.Paste
        End If
    Next
End Sub

Here's an example of sheet 1:

apple     1     2     1
banana    1     3     5
carrot    1     1     1
banana    1     2     3

And here's the New sheet:

banana    1     3     5

banana    1     2     3

Also, I didn't attempt this in my code but it would be good if there were no space between the rows.

Thanks

LV426
  • 1
  • 2
  • *Welcome to [so]!* Your question is unclear - it would be helpful if you [edit] to include sample data as well as an explanation of specifically what you're trying to do, and an example of what the data would look like in the end. See "[ask]" as well as **how to create a [mcve]". Also here's a [checklist](//codeblog.jonskeet.uk/stack-overflow-question-checklist/) from the sites' top user. Also helpful is [How to avoid using Select in Excel VBA](https://stackoverflow.com/q/10714251/8112776). – ashleedawg Sep 03 '18 at 05:38
  • Stepping through the code (with [F8]) is a good way to troubleshoot issues like this. More in Chip Pearson's article on [Debugging VBA](http://www.cpearson.com/excel/DebuggingVBA.aspx), to find out which part of your code is causing an issue. – ashleedawg Sep 03 '18 at 05:39
  • I thought it was quite clear what I wanted to do, but I have now added examples. Thanks for the link to the article on avoiding select. I'm just transitioning from recorded macros to writing from scratch. – LV426 Sep 04 '18 at 07:16
  • 1) Your `Rows` property refers to newly inserted sheet (New). Since it's empty, you copy empty cells and paste them to the same empty sheet (New). 2) Your loop processes all 1048756 cells. Is that what you need? – JohnyL Sep 04 '18 at 07:20
  • I see what you mean JohnyL but have no idea how to fix it. Also, no I only want to check up to say 500 cells – LV426 Sep 04 '18 at 07:41

1 Answers1

0

This is my edited code above to work with your sample data. Make sure that the workbook that contains the sheet that you want to copy from is active when this is run.

Sub copy_rows()
'create the new sheet
Sheets.Add After:=Sheets(1)
Sheets(2).Name = "New"

'get the number of rows you need to check
Dim RowCount As Integer
RowCount = WorksheetFunction.CountA(Sheets(1).Range("A:A"))

'iterate over the rows
For r = 0 To RowCount - 1
this_cell = Sheets(1).Range("A1").Offset(r, 0)
    If this_cell = "bananas" Then
        'iterate over the columns and copy cells to new sheet
        For c = 0 To 3
            Sheets(2).Range("A1").Offset(x, c) = Sheets(1).Range("A1").Offset(r, c)
        Next c
    'counter for the offset to next row in new sheet
    x = x + 1
    End If
Next r
End Sub
BrendanOtherwhyz
  • 471
  • 4
  • 9
  • Thanks! Very useful, but your code only copies the cell in column B - I want to copy the whole row. – LV426 Sep 04 '18 at 07:00
  • Also, the reason I am creating a new page every time is that the macro needs to work in an exported csv that only has one sheet. That's why I need to do this with VBA, so I can click a button to copy the rows, rather than enter VLOOKUP formulas - Thanks! – LV426 Sep 04 '18 at 07:01