Copy only certain lines from one sheet to another

[Maxibo]

Hi All

Have a sheet that has data in.

I would like to copy all lines from that sheet to next sheet if one column
as a particular ' variable ' in i.e in this case L which represents a live
case.

To try and explain more, say for example sheet1 has 100 names and their
adresses in with a final column with the letter L in. But only a selection
has the letter L in.

Would like to find these letter L and put them in sheet two, of course if
sheet 1 has a letter L deleted or added sheet 2 is updated automatically.

Hope I have explained ok.

TIA

p.s sorting sheet 1 and copying and pasting is an option but would like it
automated.

 

[Peo Sjoblom]

You can record a macro while you apply data>autofilter, filter on L and copy
and paste

 

[Maxibo]

Hi Peo, many thanks for the prompt reply,

My previous post tried to keep things as simple as possible. Unfortunatly
autofilter is not possible as there are sub sections in the sheet The way it
is designed the layout of the sheet cannot be altered, I can only extract
data
Sorry, not my design. I am trying to look at doing it better but lot to
review... ;-(

 

[Jef Gorbach]

First, I suggest your macro copy the entire worksheet to a scrap page before
processing so the original data isnt changed then either relayout to use
autofilter for Ls copy the visible rows -or- loop each row seperately to
find them (takes longer for same result), moving the desired rows to your
final report page.
Email a before/after sample to jefgorbach at aol and ill see if i can point
you in a possible direction.

 

[Otto Moehrbach]

You would need a Worksheet_Change event macro to do what you want
automatically. The code in the macro would react if an L were entered or an
L deleted from a specific column. If I understand you correctly, you need 2
things. One is to go through all the data in sheet1 (I presume you already
have data in sheet1)and copy those rows that have an L in a specific column.
That gives you a baseline of data. From there, you want individual lines
copied automatically if an L is added and deleted from sheet 2, again
automatically, if an L is deleted. Is that correct?
The following 3 macros do what you want. You need to make changes to
make these fit with the layout of your data. Note that, as written, these
macros assume that Sheet1 is the active sheet. It is also assumed that Row
1 in both sheets is a header row and the data starts in Row 2, at Column A.
And it is assumed that the "L" row cells have either an "L" or are blank.
As written, the code reacts if a change is made to any entry in the "L"
column (Column D as written). If the new value is "L", the code will react
as you say. If the value is anything other than an "L" the code assumes the
old value was "L" and was removed. It will then remove the corresponding
row in Sheet2.
The Copy Data macro is a one-time macro to do the initial copying of all the
"L" data to sheet2. The automation for adding and deleting the "L" is done
by the first and third macros.
The first macro needs to be placed in the sheet module of Sheet1. You
can access the sheet module by right-clicking on the sheet tab and selecting
View Code. The remaining macros go in a standard module.
If you wish, email me a valid email address for you and I'll send you a
small file that has these macros properly placed. My email address is
(e-mail address removed). Remove the "nop" from this address. HTH Otto

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column <> 4 Then Exit Sub
If Target.Row > 1 And Target.Row < Range("A" & Rows.Count).End(xlUp).Row
Then _
Call UpdateSht2(Target)
End Sub

Sub CopyData()
Dim Rng1 As Range
Dim Rng2 As Range
Dim i As Range
Dim Dest As Range
Set Rng1 = Range("A2", Range("A" & Rows.Count).End(xlUp))
With Sheets("Sheet2")
Set Dest = .Range("A" & Rows.Count).End(xlUp).Offset(1)
For Each i In Rng1
If i.Offset(, 3) = "L" Then
i.Resize(, 3).Copy Dest
Set Dest = Dest.Offset(1)
End If
Next i
End With
End Sub

Sub UpdateSht2(i As Range)
Dim Rng2 As Range
With Sheets("Sheet2")
If i.Value = "L" Then
i.Offset(, -3).Resize(, 3).Copy .Range("A" &
Rows.Count).End(xlUp).Offset(1)
Else
Set Rng2 = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
Rng2.Find(What:=i.Offset(, -3).Value,
LookAt:=xlWhole).EntireRow.Delete
End If
End With
End Sub