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

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,171
Try:
Code:
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.
 
Last edited:

danj4fsu

New Member
Joined
Jan 19, 2018
Messages
20
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
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,171

ADVERTISEMENT

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

New Member
Joined
Jan 19, 2018
Messages
20
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

Well-known Member
Joined
Apr 11, 2012
Messages
10,171

ADVERTISEMENT

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

danj4fsu

New Member
Joined
Jan 19, 2018
Messages
20
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

Well-known Member
Joined
Apr 11, 2012
Messages
10,171
Try this macro. I am assuming that you want to copy all the "held" rows from each sheet to sheet "Held Appts 4 Submission".
Code:
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

New Member
Joined
Jan 19, 2018
Messages
20
Try this macro. I am assuming that you want to copy all the "held" rows from each sheet to sheet "Held Appts 4 Submission".
Code:
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).
 

Watch MrExcel Video

Forum statistics

Threads
1,123,305
Messages
5,600,867
Members
414,410
Latest member
4610

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top