Transfer data between worksheets

[Guest]

My question is if I have one worksheet with all of my information, and I want
to extract certain data based on criteria, and cut and paste only that data
into another worksheet, is this possible? If so how would I do it?

 

[Charles Harmon]

Rob,

It can be done. What and where is your criteria? Where do you want it to go?

Charles

 

[Guest]

Are you familiar with using Filter, in particular Advanced Filter?

 

[Guest]

I would be able to use the advanced filter, but I keep getting an error
message that says I can only copy data to an active sheet. Is there a way to
have several sheets active at the same time, b/c that would pretty much solve
my problem?

Rob

 

[Charles Harmon]

Rob,

Here's something you can look at.

Sub move_this()
Application.ScreenUpdating = False
Dim i As Integer
Dim myrow
myrow = Cells(65536, 1).End(xlUp).Row
For i = myrow To 2 Step -1
If Cells(i, 1).Value = "bad" Then
Cells(i, 1).Select
Range(Selection, Selection.End(xlToRight)).Copy
Destination:=Sheet3.Cells(1, 1)
End If
Next
End Sub

 

[Tom Ogilvy]

Using an Advanced filter:

Sub AdvFilter()
With Worksheets("Sheet1")
.Range("A1").CurrentRegion.AvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=.Range("G1:G2"), _
CopyToRange:=Worksheets("Sheet2").Range("A1"), _
Unique:=False
End With
End Sub


You can do it manually by starting from the destination sheet (not the sheet
with the source data). Excel might complain that it can't find your data,
but just ignore that message and continue to select source data and so
forth.

 

[Guest]

I am in Sales, I am building a spreadsheet that I can input all of my
prospects information in Sheet 1, and as they want to continue to do
business, I will turn a cell from N to Y, and when it gets changed to Y, I
want all that persons information to get shifted to another Sheet.