Copy or replace sheet1 from a closed workbook
 

I have a workbook that fires macros when opened so users only have access
to a form to add and amend data - this workbook holds data I need in the
workbook mentioned below.

When the second workbook opens, it shoud retrieve all data in Sheet1 in the
first workbook (or simply delete current Sheet1 and replace with a copy).
Does anyone have a simple method?
--
Steve

Copy or replace sheet1 from a closed workbook
 

Hi Steve,

Try something like:

'=============
Private Sub Workbook_Open()
Dim WB As Workbook
Dim SH As Worksheet

Set WB = Workbooks("FirstBook.xls") '<<==== CHANGE
Set SH = WB.Sheets("Sheet1") '<<==== CHANGE
With Me.Sheets("Sheet1")
.Cells.Clear
SH.Cells.Copy Destination:=.Range("A1")
End With
End Sub
'<<=============

This code should be pasted into the ThisWorkbook module of the second
workbook.

Note that this code includes no error handling to allow, for example, that
the first workbook may not be open.


---
Regards,
Norman


"Steve" <No Spam wrote in message ...
I have a workbook that fires macros when opened so users only have access
to a form to add and amend data - this workbook holds data I need in the
workbook mentioned below.

When the second workbook opens, it shoud retrieve all data in Sheet1 in
the first workbook (or simply delete current Sheet1 and replace with a
copy). Does anyone have a simple method?
--
Steve

Copy or replace sheet1 from a closed workbook
 

Thank you, but only works if WB is open. I need to grab data from a closed
WB
--
Steve

"Norman Jones" wrote in message
...
Hi Steve,

Try something like:

'=============
Private Sub Workbook_Open()
Dim WB As Workbook
Dim SH As Worksheet

Set WB = Workbooks("FirstBook.xls") '<<==== CHANGE
Set SH = WB.Sheets("Sheet1") '<<==== CHANGE
With Me.Sheets("Sheet1")
.Cells.Clear
SH.Cells.Copy Destination:=.Range("A1")
End With
End Sub
'<<=============

This code should be pasted into the ThisWorkbook module of the second
workbook.

Note that this code includes no error handling to allow, for example, that
the first workbook may not be open.


---
Regards,
Norman


"Steve" <No Spam wrote in message ...
I have a workbook that fires macros when opened so users only have access
to a form to add and amend data - this workbook holds data I need in the
workbook mentioned below.

When the second workbook opens, it shoud retrieve all data in Sheet1 in
the first workbook (or simply delete current Sheet1 and replace with a
copy). Does anyone have a simple method?
--
Steve

Copy or replace sheet1 from a closed workbook
 

Hi Steve,

Thank you, but only works if WB is open. I need to grab data from a closed
WB

Try:

'=============
Private Sub Workbook_Open()
Dim WB As Workbook
Dim SH As Worksheet
Dim blClosed As Boolean

On Error Resume Next
Set WB = Workbooks("FirstBook.xls") '<<==== CHANGE
On Error GoTo 0

If WB Is Nothing Then
blClosed = True
Application.ScreenUpdating = False
Set WB = Workbooks.Open("FirstBook.xls")
End If

Set SH = WB.Sheets("Sheet1") '<<==== CHANGE
On Error GoTo 0

With Me.Sheets("Sheet1")
.Cells.Clear
SH.Cells.Copy Destination:=.Range("A1")
End With

If blClosed Then WB.Close (False)
Application.ScreenUpdating = True

End Sub
'<<=============


---
Regards,
Norman

Copy or replace sheet1 from a closed workbook
 

The book needs to remain closed to prevent a macro from firing.

In a round-about you response seems to be saying I can't copy from a closed
book. That being the case, I'll write some code to link the data then
copy/paste special.


"Norman Jones" wrote in message
...
Hi Steve,

Thank you, but only works if WB is open. I need to grab data from a
closed WB

Try:

'=============
Private Sub Workbook_Open()
Dim WB As Workbook
Dim SH As Worksheet
Dim blClosed As Boolean

On Error Resume Next
Set WB = Workbooks("FirstBook.xls") '<<==== CHANGE
On Error GoTo 0

If WB Is Nothing Then
blClosed = True
Application.ScreenUpdating = False
Set WB = Workbooks.Open("FirstBook.xls")
End If

Set SH = WB.Sheets("Sheet1") '<<==== CHANGE
On Error GoTo 0

With Me.Sheets("Sheet1")
.Cells.Clear
SH.Cells.Copy Destination:=.Range("A1")
End With

If blClosed Then WB.Close (False)
Application.ScreenUpdating = True

End Sub
'<<=============


---
Regards,
Norman

Copy or replace sheet1 from a closed workbook
 

On further thought, cured the problem by turning firing the macros in the
first WB only if the second WB is not open. Now I can stick with my original
copy from first WB code.
--
Steve

"Steve" <No Spam wrote in message ...
The book needs to remain closed to prevent a macro from firing.

In a round-about you response seems to be saying I can't copy from a
closed book. That being the case, I'll write some code to link the data
then copy/paste special.


"Norman Jones" wrote in message
...
Hi Steve,

Thank you, but only works if WB is open. I need to grab data from a
closed WB

Try:

'=============
Private Sub Workbook_Open()
Dim WB As Workbook
Dim SH As Worksheet
Dim blClosed As Boolean

On Error Resume Next
Set WB = Workbooks("FirstBook.xls") '<<==== CHANGE
On Error GoTo 0

If WB Is Nothing Then
blClosed = True
Application.ScreenUpdating = False
Set WB = Workbooks.Open("FirstBook.xls")
End If

Set SH = WB.Sheets("Sheet1") '<<==== CHANGE
On Error GoTo 0

With Me.Sheets("Sheet1")
.Cells.Clear
SH.Cells.Copy Destination:=.Range("A1")
End With

If blClosed Then WB.Close (False)
Application.ScreenUpdating = True

End Sub
'<<=============


---
Regards,
Norman