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

TomGreg

New Member
Joined
Jul 23, 2014
Messages
11
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!
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
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.
Code:
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.
 
Upvote 0
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!
 
Upvote 0
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.

 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,956
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top