Trouble with VBA for transferring rows from one sheet to another when criteria in cell is met.

[TomGreg]

Hi,

I’m having a little trouble with a VBA and I’d be very grateful for some help!
I am transferring a whole row/columns A – J when the entry ‘yes’ is entered in column E from sheets named after peoples first names e.g. Geoff, Peter, Emma etc. The sheet that these transfer to is name ‘Indemnified Summary’
Basically, I’ve been using the below code which when I run, transfers the row and contents exactly how I’d like.


Sub copyPaste()
Dim ws As Worksheet
Dim wt As Worksheet
Set ws = Sheets("Geoff")
Set wt = Sheets("Indemnified Summary")
Dim i As Long
Dim lr As Long
lr = ws.Range("E" & Rows.Count).End(xlUp).Row
Dim lt As Long

For i = 1 To lr
lt = wt.Range("A" & Rows.Count).End(xlUp).Row
If ws.Range("E" & i) = "yes" Then
ws.Range("E" & i).EntireRow.Copy wt.Range("A" & lt + 1)
End If
Next i
End Sub


However, there are a few things I’d like to alter, but don’t know how.

• Each time it runs, it duplicates on the sheet it transfers to – I only need each row to transfer once!
• I’d like to add other sheets to the same code so it applies to all needed, but I can’t seem to make that work. So as well as “Geoff” I’d like “Peter” and “Emma” and so on.
• Is there a code to make each transfer either

1. automatically whenever entries are made into source sheet or
2. whenever the workbook opens after being closed.

If anyone can help I’d be very very very grateful!

 

[WarPigl3t]

The first thing you asked about is duplicating rows each time you run the macro. There are a couple solutions but I'm going to suggest the easiest one. Make code that selects row 2 through the last row in the summary sheet and clears the contents. This code should be placed at the top of your macro to clear the data before running your code again.

lastRowWT = Range("E" & Rows.Count).End(xlup).Row
Range("A2:E" & lastRowWT).ClearContents

Now as for your next question about applying the code to other worksheets. I don't fully understand your question so I'm not going to talk about it.

Now about running the code automatically. I'm surprized you know about macros and don't know about "worksheet selection change event". Google it. If you place your macro in a selection change event rather than a Sub like you did, then it will update each time you make a change to the worksheet. You also have other options to run the macro when you click the save button or when you open the workbook. The possabilities are endless. Google it.

 

[TomGreg]

HI WarPiglet,

Thank you so much for your reply and information within.

I tried that code; however it only seemed to delete everything that that is in rows A – E in the sheet “indemnified summary” and nothing from sheet “Geoff” transferred.

I edited the code in the module as per your instructions so it appeared like thus


Sub copyPaste()
lastRowWT = Range("E" & Rows.Count).End(xlUp).Row
Range("A2:E" & lastRowWT).ClearContents
Dim ws As Worksheet
Dim wt As Worksheet
Set ws = Sheets("Geoff")
Set wt = Sheets("Non Indemnified Summary")
Dim i As Long
Dim lr As Long
lr = ws.Range("E" & Rows.Count).End(xlUp).Row
Dim lt As Long

For i = 1 To lr
lt = wt.Range("A" & Rows.Count).End(xlUp).Row
If ws.Range("E" & i) = "no" Then
ws.Range("E" & i).EntireRow.Copy wt.Range("A" & lt + 1)
End If
Next i
End Sub

Is this correct? Like I said, when I ran it, it deleted everything from columns A-E in the sheet I would like the aforementioned copied to, whilst nothing copied at all.

Whereas, without that code you suggested, when I ran the VBA it copied everything exactly as I would like, but it would always duplicate if I ran it again. As the workbook is updated and amended often, I would like this duplication not to happen!

 

[TomGreg]

In regards to the second question, my deepest apologies for not elucidating as adequately as necessary.
You may notice in the code that I am copying from a sheet named “Geoff” and in the VBA I am selecting this sheet by using the following
Set ws = Sheets("Geoff")
Is there a method for adding other sheets into this so when I run the code the data I’d like also copies into the sheet called “indemnified summary”
There are a few sheets I’d like the data to copy from, all named after people’s names.