Copy, to another sheet, a range of cells in a row if one cell has certain text.

[danj4fsu]

I hope I explain this so everyone understands. I have included a screenshot for some clarification. For every instance of "held" in column F, i want the corresponding cells A-E to be copied to another sheet.

https://www.dropbox.com/s/k7mbt18510m766u/inside sales 2.JPG?dl=0

TIA

 

[mumps]

Try:

Sub CopyRange()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Sheets("Sheet1").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Sheets("Sheet1").Range("A11:F" & LastRow).AutoFilter Field:=6, Criteria1:="held"
    Sheets("Sheet1").Range("A13:E" & LastRow).SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
    If Sheets("Sheet1").AutoFilterMode = True Then Sheets("Sheet1").AutoFilterMode = False
    Application.ScreenUpdating = True
End Sub

Change the sheet names to suit your needs.

 

[danj4fsu]

mumps

I apologize in advance but I am not familiar with code at all, only basic cell functions. Can you walk me through where to put that code??

DJ

 

[Fluff]

For adding & running a macro, have a look here
https://www.ablebits.com/office-addins-blog/2013/12/06/add-run-vba-macro-excel/

 

[mumps]

Do the following: Hold down the ALT key and press the F11 key. This will open the Visual Basic Editor. In the menu at the top click 'Insert' and then click 'Module'. Copy and paste the macro into the empty code window that opens up. Press the F5 key to run the macro. Close the code module window to return to your sheet. There are other quicker ways to run the macro such as assigning it to a button that you would click on your sheet or assigning it to a short cut key. I hope this helps.

 

[danj4fsu]

Do the following: Hold down the ALT key and press the F11 key. This will open the Visual Basic Editor. In the menu at the top click 'Insert' and then click 'Module'. Copy and paste the macro into the empty code window that opens up. Press the F5 key to run the macro. Close the code module window to return to your sheet. There are other quicker ways to run the macro such as assigning it to a button that you would click on your sheet or assigning it to a short cut key. I hope this helps.

What do I do if I want the macro to scrape more than one sheet? Where the code says Sheets("Jan")., do I just add a comma and put the other sheets in?? I have a sheet for each month and want the macro to scrape all sheets.

 

[mumps]

Are there any sheets in your workbook that you want excluded and if so what are their names?

 

[danj4fsu]

Are there any sheets in your workbook that you want excluded and if so what are their names?

The sheet names are Jan, Feb, Mar etc through Dec. Then I have the macro on sheet "Held Appts 4 Submission".

 

[mumps]

Try this macro. I am assuming that you want to copy all the "held" rows from each sheet to sheet "Held Appts 4 Submission".

Sub CopyRange()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    Dim ws As Worksheet
    For Each ws In Sheets
        If ws.Name <> "Held Appts 4 Submission" Then
            LastRow = ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            ws.Range("A11:F" & LastRow).AutoFilter Field:=6, Criteria1:="held"
            ws.Range("A13:E" & LastRow).SpecialCells(xlCellTypeVisible).Copy Sheets("Held Appts 4 Submission").Cells(Sheets("Held Appts 4 Submission").Rows.Count, "A").End(xlUp).Offset(1, 0)
            If ws.AutoFilterMode = True Then ws.AutoFilterMode = False
        End If
    Next ws
    Application.ScreenUpdating = True
End Sub

 

[danj4fsu]

Try this macro. I am assuming that you want to copy all the "held" rows from each sheet to sheet "Held Appts 4 Submission".

Sub CopyRange()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    Dim ws As Worksheet
    For Each ws In Sheets
        If ws.Name <> "Held Appts 4 Submission" Then
            LastRow = ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            ws.Range("A11:F" & LastRow).AutoFilter Field:=6, Criteria1:="held"
            ws.Range("A13:E" & LastRow).SpecialCells(xlCellTypeVisible).Copy Sheets("Held Appts 4 Submission").Cells(Sheets("Held Appts 4 Submission").Rows.Count, "A").End(xlUp).Offset(1, 0)
            If ws.AutoFilterMode = True Then ws.AutoFilterMode = False
        End If
    Next ws
    Application.ScreenUpdating = True
End Sub

I am getting a run-time error '1004':
No cells were found.

Here is a screen shot of the way each sheet is set up. I want columns "A" through "E" copied for every instance of "held" in column "F" but starting on row "13" of every sheet. I say starting on row 13 because of the header on row 12 that says "Held | Cancelled | Resheduled".

https://www.dropbox.com/sh/y4f58tg401yyelb/AACSMOaalSD5RkNrnIyd6w1Wa?dl=0

The first macro you gave me worked for sheet "Jan" worked but after talking to my team, we realized it needs to pull from all 12 sheets (Jan-Dec).