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

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 news:[email protected]...
>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
>

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

"Norman Jones" <[email protected]> wrote in message
news:[email protected]...
> 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 news:[email protected]...
>>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
>>
>
>

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

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>

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 news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>>
>>
>
>