If Cell = X, Then Copy Entire Row Into A New Sheet

  • Thread starter Thread starter Mandy
  • Start date Start date
M

Mandy

Hi, I am very new to excel. I really need some help writing a macro or
formula. Basically, I have a spreadsheet that is a list of events.

I need a macro or formula that says If row C = ADOTG , Then Copy Entire
Row Into A New Sheet. (ADOTG is the name of the event). Can anyone help
this excel beginner?
Thanks Mandy
 
Usually, I find it much, much better to keep all the data in one
location/worksheet.

Then I can use data|filter|autofilter to show the values I want. Or I can sort
the data or create charts or pivottables.

But one way to do this is to apply data|filter|autofilter, show the rows with
ADOTG and then copy the visible cells to a new worksheet.

If you really want a macro, you could record one when you do it manually.

====
But if you have other values in that column and want to separate them into
different sheets...

You may want to look at how Ron de Bruin and Debra Dalgleish approached this
kind of thing:

Ron de Bruin's EasyFilter addin:
http://www.rondebruin.nl/easyfilter.htm

Or:

Code from Debra Dalgleish's site:
http://www.contextures.com/excelfiles.html

Create New Sheets from Filtered List -- uses an Advanced Filter to create
separate sheet of orders for each sales rep visible in a filtered list; macro
automates the filter. AdvFilterRepFiltered.xls 35 kb

Update Sheets from Master -- uses an Advanced Filter to send data from
Master sheet to individual worksheets -- replaces old data with current.
AdvFilterCity.xls 55 kb
 
SORRY my request should be COLUMN C - not row c.
Thanks
Mandy

I need a macro or formula that says If COLUMN C = ADOTG , Then Copy
Entire
Row Into A New Sheet. (ADOTG is the name of the event). Can anyone help
this excel beginner?
Thanks Mandy
 
Name one sheet 'CopyFrom' and name another sheet 'CopyTo'.
Run code...........

Sub Copyx()
Dim RngColF As Range
Dim i As Range
Dim Dest As Range
Sheets("CopyFrom").Select
Set RngColF = Range("C1", Range("C" & Rows.Count).End(xlUp))
With Sheets("CopyTo")
Set Dest = .Range("A1")
End With
For Each i In RngColF
If i.Value = "x" Then
i.EntireRow.Copy Dest
Set Dest = Dest.Offset(1)
End If
Next i
End Sub

HTH,
Ryan---
 
Name one sheet 'CopyFrom' and name another sheet 'CopyTo'.  
Run code...........

Sub Copyx()
Dim RngColF As Range
Dim i As Range
Dim Dest As Range
Sheets("CopyFrom").Select
Set RngColF = Range("C1", Range("C" & Rows.Count).End(xlUp))
With Sheets("CopyTo")
Set Dest = .Range("A1")
End With
For Each i In RngColF
If i.Value = "x" Then
i.EntireRow.Copy Dest
Set Dest = Dest.Offset(1)
End If
Next i
End Sub

HTH,
Ryan---
Click to expand...

Thank you Ryan - this was very helpful!
 
How would you do this for multiple sheets? i.e., look at column C in multiple sheets and if column c = adotg copy the entire row to a new sheet
 
Back
Top