+ Reply to Thread
Results 1 to 10 of 10

Copy/Pasting in another worksheet using VBA

  1. #1
    Registered User
    Join Date
    12-06-2012
    Location
    abbotsford, bc, canada
    MS-Off Ver
    Excel 2010
    Posts
    5

    Copy/Pasting in another worksheet using VBA

    Hello,

    I'm hoping someone can help me as I am very new to VBA and I'm sorry if I don't use the correct terms . I have a workbook where each worksheet is a certain year(2012, 2013, 2014 so far). I need to write a macro that if there is a value(amount remaining at year end) in a certain cell(S23) then I would like to paste this value as well as a range of data (the person's information) into the next worksheet into a certain range to carry forward to the next year. I would then need it to look in cell(S24) and so on....I figured out how to do this with :

    Please Login or Register  to view this content.
    However I need this to happen for 30 rows in one worksheet X multiple worksheets and am wondering if I can do this with a loop as copy and pasting the code and updating the cells would take a long time. It also would be nice as then I don't have to update it for all the years.
    Right now I have it pasting into a certain range but it would nice if when the data was pasted that it searched for the first available row in the certain range so I don't end up with empty rows.

    Thank you so much for your help!!

    Moderator's Edit: Use code tags when posting code. To do so in future, select your code and click on the # icon at the top of your post window.
    Last edited by arlu1201; 12-07-2012 at 02:17 PM.

  2. #2
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Copy/Pasting in another worksheet using VBA

    It will be good if you explain with the help of a sample file.

    To Attach a File:

    1. Click on Go Advanced
    2. In the frame Attach Files you will see the button Manage Attachments
    3. Click the button.
    4. A new window will open titled Manage Attachments - Excel Forum.
    5. Click the Browse... button to locate your file for uploading.
    6. This will open a new window File Upload.
    7. Once you have located the file to upload click the Open button. This window will close.
    8. You are now back in the Manage Attachments - Excel Forum window.
    9. Click the Upload button and wait until the file has uploaded.
    10. Close the window and then click Submit.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  3. #3
    Registered User
    Join Date
    12-06-2012
    Location
    abbotsford, bc, canada
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Copy/Pasting in another worksheet using VBA

    Here is a sample of my workbook
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Copy/Pasting in another worksheet using VBA

    Amtoz,
    Initially, I have written this code, but have noticed that you have data after row 21 in Sheets("2013"). This code will go through until the last row in sheet 2012, but I am not sure If you wish to overwrite the existing data, so I have amended the code, but this code has a fixed loop, ie 23 to 37 so that it would not overwrite the data. Try both codes and let me know how you wish. I can write you a code for the other years.

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Last edited by AB33; 12-07-2012 at 04:53 PM.

  5. #5
    Registered User
    Join Date
    12-06-2012
    Location
    abbotsford, bc, canada
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Copy/Pasting in another worksheet using VBA

    Thank you so much AB33!

    I tried the last code as I do not want it to overwrite the data. I do need the data to paste into the corresponding range so I changed N=23 would this be correct? It seems to work. The problem I have is that I would need the amount from the year end (s23) to paste into (D23) in "2013" as that is the carry forward balance. I only need A23:C23 to paste and then D23 is the carry forward balance. Does that make sense?

    Also I will need this to happen in multiple sections of my worksheet. Once I have this section working I need to recreate this for 4 other branches in the same worksheet. Would I use this piece of code over again and just amend the code where N=5 to where it should paste to and then For i = 23 to 37 to the range? I did try that however only the second section of code worked.

    Thank you so much for your help!! You're a lifesaver!!

  6. #6
    Registered User
    Join Date
    12-06-2012
    Location
    abbotsford, bc, canada
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Copy/Pasting in another worksheet using VBA

    Oh I also forgot to say that if something changes on the "2012" worksheet (i.e. there no longer is a value in (S23)) as it will happen could the information on the next worksheet be cleared? I tried to accomplish this with the else part of my code
    Please Login or Register  to view this content.
    thank you so much!!

  7. #7
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Copy/Pasting in another worksheet using VBA

    Ohm!

    Okay! Let me get this right. This is now my understanding

    copy sheet("2012") rows 23-37 on the same rows in 2012(Marked as(<$500 Closing balance) and you want to do the same from 2013 to 2014. Am I right?
    What happens to the other rows? Rows 41-56. Should these rows also copied to the same rows on 2013 and 2014.
    You have marked them as (>$500 Closing balance) on rows 14 on 2013

  8. #8
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Copy/Pasting in another worksheet using VBA

    I think it could also be done automatically if something changes in sheet 2012, but Let's get the right code first. I need to absolutely clear before we go on the next stage.

  9. #9
    Registered User
    Join Date
    12-06-2012
    Location
    abbotsford, bc, canada
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Copy/Pasting in another worksheet using VBA

    I would just like to copy A23:C23 values(not the whole row) from 2012 to the same cells in 2013 and then S23 in ("2012") into D23("2013") as this is the carry forward balance. And yes then do the same for 2013 to 2014.
    And yes the same thing would have to happen for rows 41-56.

  10. #10
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Copy/Pasting in another worksheet using VBA

    Here is a long winded code,but I am sure I can modifay it further. Please note that I have included Clear cells in case there are changes to the value so that you can just re-run the codes. If you do not want them, just comment them, using '.

    You can also run this code as just one by calling it, but this is the easy bit. First let's get the right code. It is getting late now, so will carry on tom.

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.

+ 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