Copying specific rows from a sheet tab to another sheet tab by just clicking a button

Question

Hi,

Can anyone help me with this...I need to copy a set of rows from one tab to another tab of the same excel document by just clicking a button.

Also, can I also get information on how can I copy a set of rows that are hidden and paste it in the same tab without copyng the "hidden" format?

Thanks!!!

Answer 1

If 'Copystart' is your original rows, and 'Copyend' is where you want to paste them, then using 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, then add [Sheetname]. in front of the range, where Sheetname is the name of the sheet that the named range is in that you want to reference.

Answer 2

There are no native functions in Excel that will allow you to do this. You will need to write a macro and assign that to a button control (which you can drop onto your worksheet by using the Control Toolbox toolbar - View > Toolbars > Control Toolbox).

You would usually then assign the macro to that button by double-clicking the button (while it's still in Design View) and calling your macro in the newly-generated `CommandButton_Click` event. As Lance says, named ranges would be the easiest to work with.

To answer the last part of your question, programmatically copying a range doesn't copy the formatting or formula as well. It only takes the value of the cell. So regardless of whether your source range is hidden, the destination will not need to have its `visible` property explicitly set - the hidden attribute is ignored when copying.