Copying and pasting entire workbook

[phreud]

Hi,

I'm trying to write a load function which loads a workbook, copies it
contents, closes it and pastes the copied information into the workboo
that was open all the time (replacing what is already in there).
can't figure out how to do it. I really don't want to copy/paste i
sheet by sheet.


This is what I have:


Code
-------------------

Dim sFname As String

sFname = Application.GetOpenFilename( _
FileFilter:="Excel Workbooks,*.xls", _
Title:="Open a File", _
MultiSelect:=False)

If sFname <> "False" Then
Workbooks.Open sFname

Workbooks(2).Worksheets.Copy
Workbooks(2).Close False

' This is what I would like to be able to do.
Workbooks(1).Worksheets.Paste
End If

-------------------


Any ideas?

Thanks in advance

 

[RB Smissaert]

Why not just copy the file?

RBS

 

[phreud]

The file contains a large number of UserForms, making the file over 500
kB in size. I've written a save function that copy just the worksheets
and saves them (making saved files about 80 kB).

The problem is when I'm trying to load them again. I want to
compltetely replace everything in the open workbook with the contents
of the workbook in the saved file. I just can't get it to work, been
struggeling with it for 5 hrs now

I can load the saved file and paste it into a new workbook, but I can't
paste it into an existing one.

Any help would be greatly apprechiated!

 

[Dave Peterson]

Workbooks(2).Worksheets.Copy _
after:=workbooks(1).worksheets(1)

will copy the sheets to workbooks(1).

 

[phreud]

*Workbooks(2).Worksheets.Copy _
after:=workbooks(1).worksheets(1)

will copy the sheets to workbooks(1).


Dave Peterson
(e-mail address removed) *

The thing is, I need to replace the sheets in workbooks(1) with th
corresponding sheets in workbooks(2), not just paste them as ne
sheets.

I have a working (but really ugly) solution. I can copy and paste cel
by cell, and everything in the workbook (graphs etc) get update
correctly.

This is what I have now:


Code
-------------------

Workbooks.Open sFname
Workbooks(2).Worksheets("Sheet1").Range("C11").Copy
Workbooks(1).Worksheets("Sheet1").Range("C11").PasteSpecial Transpose:=True
Workbooks(2).Close False

-------------------


I want to do this for all cells in all worksheets.

Thankyou

 

[Doug Glancy]

Phreud,

It seems you could loop through the sheets in the "from" book, deleting the
corresponding named sheet from the "to" book and then copying it to the "to"
book.

Sub test()

Dim from_ws As Worksheet
Dim to_wb As Workbook

Set to_wb = Workbooks("to.xls")

On Error GoTo err_handler

Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each from_ws In Workbooks("from.xls").Worksheets
to_wb.Sheets(from_ws.Name).Delete
from_ws.Copy after:=to_wb.Worksheets(to_wb.Worksheets.Count)
Next from_ws

err_handler:
Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

hth,

Doug

 

[phreud]

Thanks Doug!

I'll look into that tomorrow. Looks like it could work. I'll get bac
with results..