Make a copy of a sheet and add it into a new workbook?

[NateBuckley]

Hello, I know how to copy and paste data from one sheet to another with ranges

but I'm just wondering if there is an easier way by simply stating something
like

Workbooks("Backup.xls").Sheets("Data") =
Workbooks("Project.xls").Sheets("Data")

Thanks in advance!!

Nate

 

[Gary''s Student]

Right from the Recorder:

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 4/21/2008 by James Ravenswood
'

'
Windows("Book1.xls").Activate
Cells.Select
Selection.Copy
Windows("Book2.xls").Activate
Range("A1").Select
ActiveSheet.Paste
End Sub

 

[NateBuckley]

Thank you, I was wondering if it was possible without all the switching
between activesheets and windows etc as I can do it that way, if there was
some way to just a one line way

But thank you anyways for that is still good.

 

[Dave Peterson]

Dim OrigRng as range
dim DestCell as range

with workbooks("backup.xls").worksheets("data")
set origrng = .range("a1:X99") 'whatever
end with

'single cell
set destcell = workbooks("project.xls").worksheets("data").range("z999")

with origrng
'resize the single cell to match the original range
destcell.resize(.rows.count,.columns.count).value = .value
end with

==========
or

Dim OrigRng as range
dim DestCell as range

with workbooks("backup.xls").worksheets("data")
set origrng = .range("a1:X99") 'whatever
end with

'single cell
set destcell = workbooks("project.xls").worksheets("data").range("z999")

origrng.copy
destcell.pastespecial paste:=xlpastevalues

 

[Gary''s Student]

Another way is just to copy the sheet from one workbook to the other. Try it
with the recorder on. This way has some real advanages:

1. custom column widths and row heights get carried over
2. defined names get carried over
3. print setings get carried over

To answer your second question, the cells can be copied without the
Activations:

r1.Copy r2

Just set r1 and r2 properly.

 

[Jon Peltier]

If you just want to copy the whole sheet into a new sheet in the target
workbook:

Workbooks("Source.xls").Worksheets("MySheet").Copy
After:=Workbooks("Target.xls").Sheets(1)

The worksheet is copied, the name is the same (or adjusted if Excel finds a
sheet with the same name already in the target workbook).

- Jon