+ Reply to Thread
Results 1 to 21 of 21

How to Copy and Paste Cells Next to Corresponding Cells

  1. #1
    Registered User
    Join Date
    10-12-2012
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    24

    How to Copy and Paste Cells Next to Corresponding Cells

    I have a list of Titles on Sheet 1 in Column 1.
    I want to copy the first Title in Column 1 on Sheet 1 and paste it on Sheet 3 in Cell A1.
    Once the Title is pasted in Cell A1 on Sheet 3, formulas will be calculated in Cells B1:D1 on Sheet 3.
    I then want to copy Cells B1:D1 on Sheet 3 and paste the values on Sheet 1 next the respective Title.
    After that is completed I want the macro to carry onto the rest of the Titles in Column 1 on Sheet 1 until every Title has its respective outputs from the calculations next to it.

    I understand that there are probably better ways to accomplish what I want and that if I attached my file you would be able to better help me but I have tried that already and it just doesn’t work out. I cannot paste my original file due to sensitive information and it is too difficult to re-create a workbook that illustrates my problem but leaves out the sensitive information. So if you could please do you best to help I would really appreciate it. I understand what I’m trying to do may be crude but for the sake of getting this project completed I want to proceed in this fashion and I will hopefully refine the workbook and code in the future. Thank you very much!

  2. #2
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: How to Copy and Paste Cells Next to Corresponding Cells

    Maybe:

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    10-12-2012
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    24

    Re: How to Copy and Paste Cells Next to Corresponding Cells

    That worked perfect when I used it on my example worksheet! When I made a few changes to adapt it to my original worksheet I get the message : "Run-time error '9': Subscript out of range". Below is the code when I changed it:

    Please Login or Register  to view this content.
    Do you see what mistake I made when adapting my code?

    Thanks a ton!

  4. #4
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: How to Copy and Paste Cells Next to Corresponding Cells

    What is your activesheet? Do you have a "Sheet6"? Or do you have "Sheet 6".

  5. #5
    Registered User
    Join Date
    10-12-2012
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    24

    Re: How to Copy and Paste Cells Next to Corresponding Cells

    My active sheet is "Sheet4" named "Screen" and I have a "Sheet6" named "Outputs".

  6. #6
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: How to Copy and Paste Cells Next to Corresponding Cells

    Maybe:

    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    10-12-2012
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    24

    Re: How to Copy and Paste Cells Next to Corresponding Cells

    I discovered that my main problem was that I did not modify the correct cells in my new code. Now I have the correct ranges being copied back and forth but when the macro finishes running I end up with every row being the exact same and I need them to vary based upon my Title. Any ideas?

    Please Login or Register  to view this content.

  8. #8
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: How to Copy and Paste Cells Next to Corresponding Cells

    I'm afraid I don't understand. Do you mean your range size will vary based on the Titles on Column B? If that is the case. How so?

  9. #9
    Registered User
    Join Date
    10-12-2012
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    24

    Re: How to Copy and Paste Cells Next to Corresponding Cells

    Kind of. For Example:


    - First the Title A Tale of Two Cities on Sheet ("Screen") at range B2 is copied onto Sheet ("Outputs") at range $A$2.
    - Then range $B$2:$AB$2 on Sheet ("Outputs") will be populated.
    - I then want the values from Sheet ("Outputs") range $B$2:$AB$2 to be pasted back to Sheet ("Screen") next to the Title A Tale of Two Cities in range C2:AC2.


    - After that I want the macro to proceed to the next Title in the list on Sheet ("Screen"). This Title would be Where the Red Fern Grows.
    - I want that title from range B3 to be copied to Sheet ("Outputs") range $A$2.
    - This will cause the range $B$2:$AB$2 to be populated.
    - I then want the values from Sheet ("Outputs") range $B$2:$AB$2 to be copied to Sheet ("Screen") next to the Title Where the Red Fern Grows in range C3:AC3.

    Thanks!

  10. #10
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: How to Copy and Paste Cells Next to Corresponding Cells

    Does this help?

    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    10-12-2012
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    24

    Re: How to Copy and Paste Cells Next to Corresponding Cells

    That almost works. The problem that I am running into is that every row ends up being the same on Worksheet ("Screen).

    It should be:
    1 A Tale of Two Cities 25 684 61033 913 etc…
    2 Where the Red Fern Grows 39 841 69554 842 etc…

    But it is:
    1 A Tale of Two Cities 25 684 61033 913 etc…
    2 Where the Red Fern Grows 25 684 61033 913 etc…


    Each Title should have its own respective calculation outputs next to it but every Title has the exact same outputs next to it.

  12. #12
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: How to Copy and Paste Cells Next to Corresponding Cells

    What determines the calculations. If would help to attach a sample file with some dummy data. "To attach a file, push the button with the paperclip (or scroll down to the Manage Attachments button), browse to the required file, and then push the Upload button".

  13. #13
    Registered User
    Join Date
    10-12-2012
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    24

    Re: How to Copy and Paste Cells Next to Corresponding Cells

    I have a attached a sample. You'll notice that when you press CommandButton1 on the first worksheet (Screen), some of the data that is populated is the same as the line above and below it. It looks as if the whole column is being filled in by what the last calculation was and that is not how I want it to be. I want each row to contain the outputs that relate to the stock symbol in the second column of each row.

    Thanks for your help!
    Attached Files Attached Files

  14. #14
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: How to Copy and Paste Cells Next to Corresponding Cells

    I'm afraid I cannot provide more assistance with this one, and I hope someone else can help you. For the life of me I can't follow the numerous calculations/links on your sample sheet to track the problem. It seems a bit more complicated than post #1 indicated.

  15. #15
    Registered User
    Join Date
    10-12-2012
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    24

    Re: How to Copy and Paste Cells Next to Corresponding Cells

    I know that my workbook is pretty crude and hard to follow. In essence this is what the workbook was created for:

    I can manually enter a stock symbol into cell A2 on worksheet "Outputs" and my workbook will return data in cells B2:F2 on worksheet "Outputs".

    Then I can manually copy the returned data from cells B2:F2 on worksheet "Outputs" and manually paste only the values onto worksheet "Screen" next to the stock symbol that I manually entered into cell A2 on worksheet "Outputs".

    I can then move on and manually copy and paste back and forth between the two worksheets.


    This process is very time consuming and I would rather have a macro that does it for me.

    Thanks!

    P.S. Try looking at the workbook I have attached here, I have hidden the irrelevant information so hopefully it makes it easier to look at.
    Attached Files Attached Files

  16. #16
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: How to Copy and Paste Cells Next to Corresponding Cells

    Another attempt:

    Please Login or Register  to view this content.

  17. #17
    Registered User
    Join Date
    10-12-2012
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    24

    Re: How to Copy and Paste Cells Next to Corresponding Cells

    Is there any way to use code that uses Copy and PasteSpecial instead of the line:

    Range(Range("C" & i), Range("G" & i)).Value = Sheets("Outputs").Range("B2:F2").Value

    It appears to me that this might be my problem:

    After I click the button all the values in column C on Sheet "Screen" are 0.77. This appears to me to be the output of the last run calculation. I think my problem is that all of the values in column C are set to equal the cell B2 on Sheet "Outputs" so when that cell changes all cells in column C on Sheet "Screen" change to that new value.

    If I could use Copy and PasteSpecial I think that the values would stay true to their respective tickers instead of changing to equal the last calculated value every time.

    Thanks!

  18. #18
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: How to Copy and Paste Cells Next to Corresponding Cells

    Here it is with pastespecial:

    Please Login or Register  to view this content.

  19. #19
    Registered User
    Join Date
    10-12-2012
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    24

    Re: How to Copy and Paste Cells Next to Corresponding Cells

    I really appreciate your efforts! Columns C, D & F are still duplicating the data from the last row. And columns E & G are returning what appear to be random values. Except for the last row which contains all of the correct information.

    I'm baffled!

  20. #20
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: How to Copy and Paste Cells Next to Corresponding Cells

    I'm sorry but I'm checking out for the weekend. I may revisit and see what I can do on Monday. I'm surprised no one else provided any input. Anyway have a nice weekend.

  21. #21
    Registered User
    Join Date
    10-12-2012
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    24

    Re: How to Copy and Paste Cells Next to Corresponding Cells

    Thanks! I was surprised too.

    Have a great weekend!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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