+ Reply to Thread
Results 1 to 11 of 11

copying various cells in ot different cells in a different sheet

  1. #1
    Registered User
    Join Date
    07-25-2007
    Posts
    40

    copying various cells in ot different cells in a different sheet

    i have a button and code so that when i click the button it prints, saves and creates a new worksheet ie job 2 ready for data entry,

    somewhere in that process i would like it to copy the contents of certain cells from sheet job 1 to sheet job list,

    but if i did
    Please Login or Register  to view this content.

    it will only copy that one cell, i want it to return back to job * and copy the next cell and so on,

    but it must also be in the next avaliable blanck cell down on the job list

    so where copying the contents in to cell A2 of the job list for Job 1, job 2 must be in A3, job 3 in A4 and so on

    these sheets are all in the same workbook if that mkes it easier
    Last edited by VBA Noob; 07-25-2007 at 08:51 AM.

  2. #2
    Forum Expert
    Join Date
    11-23-2005
    Location
    Rome
    MS-Off Ver
    Ms Office 2016
    Posts
    1,628
    Try with this code:
    Please Login or Register  to view this content.
    Regards,
    Antonio

  3. #3
    Registered User
    Join Date
    07-25-2007
    Posts
    40
    cheers, what is range F?

  4. #4
    Forum Expert
    Join Date
    11-23-2005
    Location
    Rome
    MS-Off Ver
    Ms Office 2016
    Posts
    1,628
    I am sorry because I used sheet 'sheet1' while you have sheet 'job 1'.

    I used range 'F' to 'S' because you wrote:
    Please Login or Register  to view this content.
    If I am in error please tell me and I will do the possible to correct my code.

    Regards,
    Antonio

  5. #5
    Registered User
    Join Date
    07-25-2007
    Posts
    40
    Ah right, your not in error i just didnt understand, ive only been learning how ot do VB in excell for 3 days

    no worries on the sheet names i can work those out

    i didnt explain my self correctly

    what im after is this:-

    i want ot copy like this

    Sheet Job 1 Cell F2 - Sheet Job List Cell A6
    Sheet Job 1 Cell G2 - Sheet Job List Cell B6
    Sheet Job 1 Cell H2 - Sheet Job List Cell C6
    Sheet Job 1 Cell S2:U2 - Sheet Job List Cell D6:F6
    Sheet Job 1 Cell F6:S6 - Sheet Job List Cell G6:N6
    Sheet Job 1 Cell H4:K4 - Sheet Job List Cell O6:R6
    Sheet Job 1 Cell F8:S8 - Sheet Job List Cell S6:AA6
    Sheet Job 1 Cell AB10:AF10 - Sheet Job List Cell AB6:AE6
    Sheet Job 1 Cell S4:U4 - Sheet Job List Cell AF6:AI6
    Sheet Job 1 Cell V4:Y4 - Sheet Job List Cell AJ6:AM6
    Sheet Job 1 Cell K59:R59 - Sheet Job List Cell AN6:AP6

    that fine and reletivly easy.

    the macro i have at the mo, save, copys the worksheet Job 1, and renames it job 2, so now i have 3 tabs in the work book, job 2 gets filled out and those cells need copying in to job list to, but 1 row down ie:

    Sheet Job 2 Cell F2 - Sheet Job List Cell A7
    Sheet Job 2 Cell G2 - Sheet Job List Cell B7
    Sheet Job 2 Cell H2 - Sheet Job List Cell C7
    Sheet Job 2 Cell S2:U2 - Sheet Job List Cell D7:F7
    Sheet Job 2 Cell F6:S6 - Sheet Job List Cell G7:N7
    Sheet Job 2 Cell H4:K4 - Sheet Job List Cell O7:R7
    Sheet Job 2 Cell F8:S8 - Sheet Job List Cell S7:AA7
    Sheet Job 2 Cell AB10:AF10 - Sheet Job List Cell AB7:AE7
    Sheet Job 2 Cell S4:U4 - Sheet Job List Cell AF7:AI7
    Sheet Job 2 Cell V4:Y4 - Sheet Job List Cell AJ7:AM7
    Sheet Job 2 Cell K59:R59 - Sheet Job List Cell AN7:AP7

    and repeat the process for Job 3, 4, 5........ 50 etc

    i can email you my workbook if that helps

    ive tried to use

    activesheet(range("H4:k4").select
    selection.copy

    then copy to job list, but i cant work out how to get it ot go back to the latest job to select the next cell to copy
    Last edited by MonkeyDeath; 07-25-2007 at 09:26 AM.

  6. #6
    Registered User
    Join Date
    07-25-2007
    Posts
    40
    ive uploaded my workbook so you can see what ive done 9i know there are too many selections in the macros and it does look a bit messy but it is still work in progress. i just want ot get it working before i tidy it up
    Attached Files Attached Files

  7. #7
    Forum Expert
    Join Date
    11-23-2005
    Location
    Rome
    MS-Off Ver
    Ms Office 2016
    Posts
    1,628
    See tha attached file... I modified the macro 'Final_Code()' to copy cells as you told in your e-mail.

    I think there are some problems to copy some ranges because the source sheet is protected and it's impossible to select a part of some united cells.

    Regards,
    Antonio
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    07-25-2007
    Posts
    40
    Quote Originally Posted by antoka05
    See tha attached file... I modified the macro 'Final_Code()' to copy cells as you told in your e-mail.

    I think there are some problems to copy some ranges because the source sheet is protected and it's impossible to select a part of some united cells.

    Regards,
    Antonio
    what do you mean united cell, if you mean merged i dont want part of a cell i want the whole contents of the cell if that makes sence.

    oh and i should have said the password is wyg

    what i ve done is unprotect the whole sheet and then at the end of the macro is protect ot get around that.

    but thanks alot, i'll have a look

  9. #9
    Registered User
    Join Date
    07-25-2007
    Posts
    40
    Quote Originally Posted by antoka05
    See tha attached file... I modified the macro 'Final_Code()' to copy cells as you told in your e-mail.

    I think there are some problems to copy some ranges because the source sheet is protected and it's impossible to select a part of some united cells.

    Regards,
    Antonio
    what do you mean united cell, if you mean merged i dont want part of a cell i want the whole contents of the cell if that makes sence.

    oh and i should have said the password is wyg

    what i ve done is unprotect the whole sheet and then at the end of the macro is protect ot get around that.

    but thanks alot, i'll have a look

  10. #10
    Registered User
    Join Date
    07-25-2007
    Posts
    40
    Quote Originally Posted by antoka05
    See tha attached file... I modified the macro 'Final_Code()' to copy cells as you told in your e-mail.

    I think there are some problems to copy some ranges because the source sheet is protected and it's impossible to select a part of some united cells.

    Regards,
    Antonio
    what do you mean united cell, if you mean merged i dont want part of a cell i want the whole contents of the cell if that makes sence.

    oh and i should have said the password is wyg

    what i ve done is unprotect the whole sheet and then at the end of the macro is protect ot get around that.

    but thanks alot, i'll have a look

  11. #11
    Registered User
    Join Date
    07-25-2007
    Posts
    40
    Quote Originally Posted by antoka05
    See tha attached file... I modified the macro 'Final_Code()' to copy cells as you told in your e-mail.

    I think there are some problems to copy some ranges because the source sheet is protected and it's impossible to select a part of some united cells.

    Regards,
    Antonio
    what do you mean united cell, if you mean merged i dont want part of a cell i want the whole contents of the cell if that makes sence.

    oh and i should have said the password is wyg

    what i ve done is unprotect the whole sheet and then at the end of the macro is protect ot get around that.

    but thanks alot, i'll have a look

+ 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