+ Reply to Thread
Results 1 to 15 of 15

Copying data from sheet to sheet using VB

  1. #1
    Forum Contributor EdMac's Avatar
    Join Date
    01-23-2006
    Location
    Exeter, UK
    MS-Off Ver
    2003
    Posts
    1,264

    Copying data from sheet to sheet using VB

    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
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    12-29-2004
    Location
    Michigan, USA
    MS-Off Ver
    2013
    Posts
    2,208
    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
    Attached Files Attached Files

  3. #3
    Forum Contributor EdMac's Avatar
    Join Date
    01-23-2006
    Location
    Exeter, UK
    MS-Off Ver
    2003
    Posts
    1,264
    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

  4. #4
    Forum Expert
    Join Date
    12-29-2004
    Location
    Michigan, USA
    MS-Off Ver
    2013
    Posts
    2,208
    Ok, the count is 0-1, let's see if the curve ball gets by...
    Attached Files Attached Files

  5. #5
    Forum Contributor EdMac's Avatar
    Join Date
    01-23-2006
    Location
    Exeter, UK
    MS-Off Ver
    2003
    Posts
    1,264
    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

  6. #6
    Forum Expert
    Join Date
    12-29-2004
    Location
    Michigan, USA
    MS-Off Ver
    2013
    Posts
    2,208
    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

  7. #7
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202
    I can't see where you give TgtRow a value.
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  8. #8
    Forum Expert
    Join Date
    12-29-2004
    Location
    Michigan, USA
    MS-Off Ver
    2013
    Posts
    2,208
    It is in the sheet change event of referrals sheet.

    Jason

  9. #9
    Forum Contributor EdMac's Avatar
    Join Date
    01-23-2006
    Location
    Exeter, UK
    MS-Off Ver
    2003
    Posts
    1,264

    Not quite right.....

    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

  10. #10
    Forum Expert
    Join Date
    12-29-2004
    Location
    Michigan, USA
    MS-Off Ver
    2013
    Posts
    2,208
    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

  11. #11
    Forum Contributor EdMac's Avatar
    Join Date
    01-23-2006
    Location
    Exeter, UK
    MS-Off Ver
    2003
    Posts
    1,264
    Oops, here it is - It was uploaded but seems to have disappeared into the ether!

    Ed
    Attached Files Attached Files

  12. #12
    Forum Expert
    Join Date
    12-29-2004
    Location
    Michigan, USA
    MS-Off Ver
    2013
    Posts
    2,208
    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

  13. #13
    Forum Contributor EdMac's Avatar
    Join Date
    01-23-2006
    Location
    Exeter, UK
    MS-Off Ver
    2003
    Posts
    1,264

    Thanks

    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

  14. #14
    Forum Contributor EdMac's Avatar
    Join Date
    01-23-2006
    Location
    Exeter, UK
    MS-Off Ver
    2003
    Posts
    1,264

    Just the job

    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

  15. #15
    Forum Expert
    Join Date
    12-29-2004
    Location
    Michigan, USA
    MS-Off Ver
    2013
    Posts
    2,208
    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1