Clever Geek Handbook

Copy specific rows from a sheet tab to another sheet tab with a simple click of a button

I need to copy a set of rows from one tab to another tab of the same Excel document by simply clicking a button.

Also, can I also get information on how I can copy the hidden rowset and paste it into the same tab without copying the "hidden" format?

0


source to share


2 answers


If "Copystart" is your original strings and "Copyend" is where you want to paste them, then use named ranges:

Sub Copybutton_Click()

Range("Copyend").value = Range("Copystart").value
Range("Copyend").visible = True

End Sub


If you have multiple named ranges with the same name, add [Sheet Name]. before the sheet, where Sheetname is the name of the sheet in which the named range is in what you want to reference.

+1


source


Excel does not have built-in functions that will allow you to do this. You will need to write a macro and assign it to a control button (which you can drop into a worksheet using the Toolbox Tool - View> Toolbars> Control Toolbox).

Typically, you assign a macro to this button by double-clicking the button (while it is still in Design view) and invoking your macro in the newly created CommandButton_Click event. As Lance says, the named ranges would be the easiest to work with.



To answer the last part of your question, copying a range programmatically does not copy formatting or formula. It takes the value of the cell. Therefore, regardless of whether your original range is hidden, the target does not need to explicitly specify its `visible` property - the hidden attribute is ignored when copied.

0


source






More articles:


All Articles