Problem copying cells contents from one worksheet to another.

    Can anyone help me with an excel vba userform issue im having?


    I’m trying to copy and paste data in a cell from one worksheet to another, but when I try to paste the data into the second worksheet, it pastes it into the same cell, but on the first worksheet??


    Im launching a userform via a macro on the first worksheet, called “Risk Assessment”. This switches over to the second worksheet, called “Hazard Selector”, and loads my userform.


    Heres the code for that macro, assigned to a button in the “risk assessment” worksheet.



    Sub HazardWizard()


    Sheets("Hazard Selector").Select


    UserForm2.Show


    End Sub



    I then use the form to make some selections in the “Hazard Selector” sheet, which put data into cell D11 on the “Hazard Selector” sheet.


    Using a button on the userform I then want to copy this D11 data, switch back to the “Risk Assessment” worksheet and close the userform. Im using this code to do this, assigned to a button on the Userform.



    Private Sub CommandButton1_Click()


    Range("D11").Copy


    Sheets("Risk Assessment").Select


    Unload Me


    End Sub


    This seems to work, and I have the contents of cell D11 on my clipboard, however, when I now try and paste this data into my “Risk Assessment” worksheet, for example into cell A1- nothing happens.


    Instead, the contents paste into the “Hazard Selector” worksheet instead, into cell A1?!


    I cant for the life of me figure out why, can anyone help?!



    Many thanks

  • rory

    Added the Label Cross Post

    Welcome to the forum. :)


    Please take a minute to read the forum rules especially as they relate to cross-posting (4b), and make sure to follow them in future.


    Cross-posted at:

    Problem copying cells contents from one worksheet to another.


    If you have posted anywhere else as well, please supply the links.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    Also, please read the Forum Rules to understand how the Forum works and why I have added Code Tags to your post


    All VBA code posted in the forum must be wrapped in code tags, which you omitted, including single-line code snippets.Be sure to use them in future posts.


    How to use code tags


    Just highlight all of the code and press the <> in the post menu above button to add the code tags.


    Thanks.

    If the code is written correctly, you do not need to activate a sheet to use a UserForm.


    You do not specify a cell to paste to. In fact you do not need to copy nd paste.


    Whichever sheet is active this code will transfer the data.


    Code
    Private Sub CommandButton1_Click()
Sheets("Risk Assessment").Range("D11") = Sheets("Hazard Selector").Range("D11")
Unload Me
End Sub

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login