VBA to copy from one sheet to another

Posted by John on October 30, 2001 10:22 AM

I am having a hardtime transfering data that I have in named cells from sheet1 to sheet2 in the same workbook. I am also pretty certain that I don't understand how to correctly select sheets as active. I created a commandbutton on sheet1 to do work on sheet2 and it isn't working right for me. How do I identify where the source and destination sheets are.

Thanks in advance for your help.

John

Posted by Barrie Davidson on October 30, 2001 10:28 AM

John, try using a button (from the Forms Toolbar) instead of using a commandbutton (from the Control Toolbox Toolbar). I think a commandbutton only works on the sheet it resides. You can assign a macro to the button by right clicking on the button and selecting Assign Macro.

Hope this helps you out.
BarrieBarrie Davidson

Posted by Juan Pablo on October 30, 2001 11:52 AM

Barrie,

This is and isn't true, you can put the code right in the sheet module like you do on a UserForm, but you can also refer to it like this.

Sheet1.CommandButton1

Juan Pablo

Posted by Barrie Davidson on October 30, 2001 11:57 AM

Juan, would you mind providing the syntax if I wanted to put a commandbutton in Sheet1 and have it copy a range named "CopyRange" to Sheet2 (A1) when clicked (this is an opportunity to learn something that I just don't want to pass up)?

Sincerely,
Barrie

Posted by Thomas Harmer on October 30, 2001 3:15 PM

Perhaps I'm missing something, but is it not merely :-

Private Sub CommandButton1_Click()
Sheets("Sheet1").Range("CopyRange").Copy Sheets("Sheet2").Range("A1")
End Sub

Posted by Juan Pablo on October 30, 2001 5:53 PM

I'm sorry for the long reply, but...

my internet connection just drop and till now i got it up, the code is the same i was going to post, with just one more thing, to avoid any unexpected errors, you should add something like

ActiveCell.Activate

To the beggining of the macro to let the worksheet regain focus.

Juan Pablo