copy rows to new sheet based on specific cell value
 

Is it possible to copy an entire row to a new workbook based on a specific
cell's value? Specifically, if the cell contains a "1" I would like the
entire row to be copied to a new workbook. I know this can be done manually
by sorting, copying and pasting, but the file is large and there are a few
hundred rows that meet the criteria weekly. Any help is appreciated - it now
takes a considerable amount of time to do this.

Answer: copy rows to new sheet based on specific cell value
 

Yes, it is possible to copy an entire row to a new workbook based on a specific cell's value using a simple VBA macro. Here are the steps to do it:

1. Open the workbook that contains the data you want to copy.
2. Press Alt + F11 to open the Visual Basic Editor.
3. In the Editor, click on Insert Module to create a new module.
4. In the module, paste the following code:
   
   
   Formula:

   ---

   Sub CopyRows()
    Dim SourceSheet As Worksheet
    Dim TargetSheet As Worksheet
    Dim LastRow As Long
    Dim i As Long
    
    Set SourceSheet = ThisWorkbook.Sheets("Sheet1") 'Replace "Sheet1" with the name of your source sheet
    Set TargetSheet = Workbooks.Add.Sheets(1) 'Creates a new workbook and sets the target sheet to the first sheet
    
    LastRow = SourceSheet.Cells(Rows.Count, "A").End(xlUp).Row 'Finds the last row in column A
    
    For i = 1 To LastRow
        If SourceSheet.Cells(i, "A").Value = 1 Then 'Checks if the value in column A is 1
            SourceSheet.Rows(i).Copy TargetSheet.Rows(TargetSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1) 'Copies the entire row to the target sheet
        End If
    Next i
    
    TargetSheet.Columns.AutoFit 'Adjusts the column width in the target sheet
End Sub 


   ---

5. Replace "Sheet1" with the name of your source sheet in the code.
6. Save the workbook as a macro-enabled workbook (.xlsm).
7. Close the Visual Basic Editor.
8. Go back to the workbook with the data you want to copy and press Alt + F8 to open the Macro dialog box.
9. Select the "CopyRows" macro and click Run.
10. The macro will create a new workbook and copy all the rows with a value of 1 in column A to the first sheet of the new workbook.

copy rows to new sheet based on specific cell value
 

Hi dlballard

See
http://www.rondebruin.nl/copy5.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




"dlballard" wrote in message ...
Is it possible to copy an entire row to a new workbook based on a specific
cell's value? Specifically, if the cell contains a "1" I would like the
entire row to be copied to a new workbook. I know this can be done manually
by sorting, copying and pasting, but the file is large and there are a few
hundred rows that meet the criteria weekly. Any help is appreciated - it now
takes a considerable amount of time to do this.

copy rows to new sheet based on specific cell value
 

Hi Ron,

Thanks - this is a bit outside my normal use of excel, but I'm determined!
I've had a quick read through the information and it seems to be just what
I'm looking for. The steps are detailed and clear, so I should be ok - it
may take some time due to my lack of experience with macros, but the time
it'll save me later will be well worth any time spent now. I'll let you know
how it works out.

Thanks again,

dlballard


"Ron de Bruin" wrote:

Hi dlballard

See
http://www.rondebruin.nl/copy5.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




"dlballard" wrote in message ...
Is it possible to copy an entire row to a new workbook based on a specific
cell's value? Specifically, if the cell contains a "1" I would like the
entire row to be copied to a new workbook. I know this can be done manually
by sorting, copying and pasting, but the file is large and there are a few
hundred rows that meet the criteria weekly. Any help is appreciated - it now
takes a considerable amount of time to do this.

copy rows to new sheet based on specific cell value
 

Hi Ron,

Just wanted to let you know that your suggestion worked like a charm! It
took me a bit of time, but now it does exactly what I was looking for. Thank
you so much!

dani


"dlballard" wrote:

Hi Ron,

Thanks - this is a bit outside my normal use of excel, but I'm determined!
I've had a quick read through the information and it seems to be just what
I'm looking for. The steps are detailed and clear, so I should be ok - it
may take some time due to my lack of experience with macros, but the time
it'll save me later will be well worth any time spent now. I'll let you know
how it works out.

Thanks again,

dlballard


"Ron de Bruin" wrote:

Hi dlballard

See
http://www.rondebruin.nl/copy5.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




"dlballard" wrote in message ...
Is it possible to copy an entire row to a new workbook based on a specific
cell's value? Specifically, if the cell contains a "1" I would like the
entire row to be copied to a new workbook. I know this can be done manually
by sorting, copying and pasting, but the file is large and there are a few
hundred rows that meet the criteria weekly. Any help is appreciated - it now
takes a considerable amount of time to do this.

copy rows to new sheet based on specific cell value
 

Hi Dani

Thanks for the feedback

Have fun with Excel VBA

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




"dlballard" wrote in message ...
Hi Ron,

Just wanted to let you know that your suggestion worked like a charm! It
took me a bit of time, but now it does exactly what I was looking for. Thank
you so much!

dani


"dlballard" wrote:

Hi Ron,

Thanks - this is a bit outside my normal use of excel, but I'm determined!
I've had a quick read through the information and it seems to be just what
I'm looking for. The steps are detailed and clear, so I should be ok - it
may take some time due to my lack of experience with macros, but the time
it'll save me later will be well worth any time spent now. I'll let you know
how it works out.

Thanks again,

dlballard


"Ron de Bruin" wrote:

Hi dlballard

See
http://www.rondebruin.nl/copy5.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




"dlballard" wrote in message ...
Is it possible to copy an entire row to a new workbook based on a specific
cell's value? Specifically, if the cell contains a "1" I would like the
entire row to be copied to a new workbook. I know this can be done manually
by sorting, copying and pasting, but the file is large and there are a few
hundred rows that meet the criteria weekly. Any help is appreciated - it now
takes a considerable amount of time to do this.