Command Button Code for Copy to Clipboard

supermanjohn01

New Member
Joined
Jul 10, 2018
Messages
12
What is the code I would use in a command button to copy the excel page, so it can be posted to a Word Document and others?
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,610
Code:
Option Explicit


Sub MakeCopy()
 
    Sheets("Sheet1").UsedRange.Copy
   
End Sub
 

supermanjohn01

New Member
Joined
Jul 10, 2018
Messages
12
Thank you for your answer, but I can't get it to work. Assume I do not know anything, because I practically do not. When I pasted it, the Sub MakeCopy() turned red, and the button did nothing once I clicked on it. So, what am I missing?
 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,610
.
Change "Sheet1" to the name of your sheet tab.
 

supermanjohn01

New Member
Joined
Jul 10, 2018
Messages
12
I still can't get it to do anything. This is what I have in there, and I click on it and it doesn't do anything. So, what am I doing wrong?

Private Sub CommandButton9_Click()
Option Explicit

Sub MakeCopy()

Sheets("Summary").UsedRange.Copy

End Sub
 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,610
.
This page gives instructions and visuals : http://www.homeandlearn.org/add_a_button_to_a_spreadsheet.html
The upper half of the page is what you should read.


If the button you are using is named "CommandButton9" ... then the macro would look like this :

Code:
[COLOR=#333333]Private Sub CommandButton9_Click()[/COLOR]

[COLOR=#333333]Sheets("Summary").UsedRange.Copy[/COLOR]

[COLOR=#333333]End Sub
[/COLOR]



You can also paste a CommandButton on the worksheet and connect it to this macro:


Code:
[COLOR=#333333]Sub MakeCopy()[/COLOR]

[COLOR=#333333]Sheets("Summary").UsedRange.Copy[/COLOR]

[COLOR=#333333]End 
[/COLOR]


In this case, you've named the macro MakeCopy rather than the name of the command button. You can name your macros anything you want, it doesn't matter. Giving them
descriptive names provides a means of understanding what the macro does without having to test it or read all the way through the lines of code in the macro.

Here is a ridiculous example : Sub ThisMacroCopiesCellsA1nB1toColumnHCell16 ()


A better way of doing the same thing is to use the second macro name above and put a comment inside the macro like this :

Code:
Sub MakeCopy ()

'This macro copies data from A1:B1 and pastes to H16

macro code here

End Sub
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,109,538
Messages
5,529,430
Members
409,876
Latest member
Akash Yadav
Top