Copying data from sheet to sheet using VB

**EdMac** · 09-21-2007, 10:16 AM

Hi All,

Unfortunately my VB skills don't reach the level I need to be able to do this.

I have attached my workbook. This has 3 sheets that contain data to be manipulated plus a data page.

The first sheet 'Referrals' is a permanent record, data remains in it for ever.

The second sheet 'database' needs to contain copied data from 'Referrals' when an entry is made in col P of 'Referrals'.

When an entry is made in col P of 'Referrals' the following is required to be copied into the next empty line in 'Database'

Col O 'Referrals' to Col A 'Database'
Cols B,C,D,E 'Referrals' to col B,C,D,E 'Database'
Cols G,H,I,J,K,L 'Referrals' to col L,M,N,O,P,Q 'Database'

Information in 'Database' will be required to be removed from time to time and copied to the sheet 'Leavers' which is another permanent record.

When an entry is made in Col AI of 'Database', the whole line (A:AI) should be cut and pasted into the next free line in 'Leavers' starting at col B. The data in 'Database' col AI also needs to be be copied to 'Leavers' col A.

If cutting the line from 'Database' leaves a gap, the entries below should be moved up.

Grateful any help you can provide.

Ed

**jasoncw** · 09-21-2007, 11:08 AM

Hi, Ed. I started working on this, and I think I finished it up, but I have not tested it. Please try it out and see how it works.

Jason

**EdMac** · 09-21-2007, 11:27 AM

Hi Jason,

Thanks for getting on the case.

I've just given it a quick trial and it seems to only copy the date from sheet to sheet - it's better than I could manage but the rest seems to be missing.

Regards

Ed

**jasoncw** · 09-21-2007, 12:04 PM

Ok, the count is 0-1, let's see if the curve ball gets by...

**EdMac** · 09-21-2007, 12:20 PM

Hi Jason,

That's looking encouraging - I've done a very quick test and it seems to work OK.

I'll have a more thorough look later and get back to you if there are any problems.

Thanks a lot for the effort you have put in - the beer is on me if I ever get to Mitchigan!

All the best

**jasoncw** · 09-21-2007, 12:50 PM

Glad I could help, Ed. Let me know if you encounter any issues with it, and I'll try to help resolve them.

Looking forward to that beer!

Jason

**royUK** · 09-21-2007, 02:35 PM

I can't see where you give TgtRow a value.

**jasoncw** · 09-21-2007, 02:45 PM

It is in the sheet change event of referrals sheet.

Jason

**EdMac** · 11-01-2007, 07:45 AM

Hi Guys,

jasonCW gave me a lot of help on this project. Since then, I've been playing with it and made a few changes and have become aware of some problems

1. When information is cut and pasted from the 'database' sheet, the row gets deleted and the table gets smaller and smaller. What I want is for this sheet to remain constant with entries coming into it and then leaving it but with the table remaining intact.

Deleting the row means that some dependent sheets ('care manager', 'basic 'details' and 'doc details') get messed up as they lose their source data.

I would like the database sheet to be protected - no password - to avoid accidental overtyping of some lookup formulae.

I attach the workbook - any suggestions (as ever) gratefully received.

Ed

**jasoncw** · 11-01-2007, 09:37 AM

Hi again, Ed. You forgot to attach your revised workbook. I will try to take a look at it later today to see what I can come up with.

Jason

**EdMac** · 11-01-2007, 10:29 AM

Oops, here it is - It was uploaded but seems to have disappeared into the ether!

Ed

**jasoncw** · 11-01-2007, 01:30 PM

Ok, Ed, I take a quick look at it. Originally you wanted the row to be deleted in the Database sheet if it was not the last row in the table. So are you saying that you want to delete the data, and just move the other data up one row? See if this will work for you:

In the Copy_To_Leavers procedure, change this:

Please Login or Register  to view this content.

to this:

Please Login or Register  to view this content.

HTH

Jason

**EdMac** · 11-01-2007, 03:15 PM

Hi Jason,

Many thanks for getting on the case so quickly - it's late here now so I'll wait till the morning to test and let you know how it goes.

regards

Ed

**EdMac** · 11-02-2007, 06:46 AM

Hi Jason,

What can I say? It is fantastic and as far as I can see does exactly what I want. I've just added a few lines of code to unprotect and protect the sheets on the macro runs.

Thanks again - a case of virtual beer is well deserved - I just wish I had your skills in VB.

Regards

Ed

**jasoncw** · 11-02-2007, 08:03 AM

Glad to see it worked for you, Ed. As far as skills go, I'm not that skilled compared to a lot of guys on this board. It just takes interest, time, and reading this board to get to my knowledge level.

And thanks for the "beer." I will be virtually drunk at work very soon. LOL!

Jason