A macro that copies entire worksheet to another

[Jim A]

Hi-
Can anybody help me with a macro that copies an entire worksheet to another
(or creates a new worksheet named by a value in a cell)? I plan to use a
push button to copy this data.
I'd appreciate any help - Jim Ayers

 

[Engin Tarhan]

Hi Jim,

Why don't you revert the easiest and the most illustrative method of writing
a macro (and learning how to write a macro) by using the macro recording
facility?

There may be slight changes depending on your Excel version, but generally
you click
Tools --> Macro --> Record New Macro,
then mimic exactly what you want to happen, stopping recording at the proper
point,
then open the macro and make fine adjustments.

Here's the output I obtained using my own advice:

--------
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 03.01.2009 by Engin Tarhan
'

'
Cells.Select
Selection.Copy
Sheets("Sheet2").Select
Cells.Select
ActiveSheet.Paste
End Sub
----------

This code copies all the cells of a sheet to an existing sheet. Creating a
new sheet using the name of some value in a cell is certainly more tricky,
but I leave the joy of achieving the goal to you.

Actually, copying all the cells of a worksheet is equivalent to copying that
worksheet under a new name, and may be a better way of doing your second
option, if you want the new sheet to include the contents of the first one.

Good luck,
Engin Tarhan

 

[Shane Devenshire]

Hi,

I second Engin's comment about learning to program and using the recorder.
But in addition it should be noted that not everything can be recorded, so
here is another sample code to copy the Sheet1, put it to the left of the
only Sheet1 and rename is with the entry in cell A1 of the original Sheet1.

Sub CopySheet()
Sheet2.Copy Sheet2
ActiveSheet.Name = Sheet2.[A1]
End Sub