I made a mistake and had to delete the sheet from my xls. Can I
import the sheet from a backup? Not the data - the whole sheet with
colours and formating as well..
Is that possible?

Thanks - Kirk

Hi Kirk,

Try something like:

'=============
Public Sub Tester002()
Dim WB1 As Workbook
Dim WB2 As Workbook
Dim SH As Worksheet

Set WB1 = Workbooks("Backup.xls") '<<==== CHANGE
Set WB2 = Workbooks("YourBook.xls") '<<==== CHANGE
Set SH = WB1.Sheets("MASTER") '<<==== CHANGE

SH.Copy Befo=WB2.Sheets(3)
End Sub
'<<=============



---
Regards,
Norman



"kirkm" wrote in message
...
I made a mistake and had to delete the sheet from my xls. Can I
import the sheet from a backup? Not the data - the whole sheet with
colours and formating as well..
Is that possible?

Thanks - Kirk

Kirk,

If you haven't saved the workbook and if you haven't made many
other changes since the deletion then you may be able to "undo" the
deletion. Go to the Edit menu and click the undo menu item until
the worksheet reappears. You might be in luck.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"kirkm"

wrote in message
I made a mistake and had to delete the sheet from my xls. Can I
import the sheet from a backup? Not the data - the whole sheet with
colours and formating as well..
Is that possible?

Thanks - Kirk

On Tue, 3 Oct 2006 09:34:36 +0100, "Norman Jones"
wrote:

Hi Kirk,

Try something like:

'=============
Public Sub Tester002()
Dim WB1 As Workbook
Dim WB2 As Workbook
Dim SH As Worksheet

Set WB1 = Workbooks("Backup.xls") '<<==== CHANGE
Set WB2 = Workbooks("YourBook.xls") '<<==== CHANGE
Set SH = WB1.Sheets("MASTER") '<<==== CHANGE

SH.Copy Befo=WB2.Sheets(3)
End Sub
'<<=============

Hi Norman

I keep getting 'subscript out of range'.
Where should that code go - in a new .xls or the one I
want to import to?

I presume the xxx.xls string includes the full path ?
(Although I tried with and without).

Thanks - Kirk

On Tue, 3 Oct 2006 01:37:51 -0700, "Jim Cone"
wrote:

Thanks Jim but I'd saved and mucked about too much.

Cheers - Kirk

Hi Kirk,

I keep getting 'subscript out of range'.

On which line?

The message indicates that the relevant workbook or sheet name is not found.
This may be the result o a typing error or an inadvertently added/deleted
trailing space etc.



---
Regards,
Norman



"kirkm" wrote in message
...
On Tue, 3 Oct 2006 09:34:36 +0100, "Norman Jones"
wrote:

Hi Kirk,

Try something like:

'=============
Public Sub Tester002()
Dim WB1 As Workbook
Dim WB2 As Workbook
Dim SH As Worksheet

Set WB1 = Workbooks("Backup.xls") '<<==== CHANGE
Set WB2 = Workbooks("YourBook.xls") '<<==== CHANGE
Set SH = WB1.Sheets("MASTER") '<<==== CHANGE

SH.Copy Befo=WB2.Sheets(3)
End Sub
'<<=============

Hi Norman

I keep getting 'subscript out of range'.
Where should that code go - in a new .xls or the one I
want to import to?

I presume the xxx.xls string includes the full path ?
(Although I tried with and without).

Thanks - Kirk

Hi Norman.

Obviously, it's all your fault. <grin

You held Kirks hand only three times out of four.

<snip
Set WB1 = Workbooks("Backup.xls") '<<==== CHANGE
Set WB2 = Workbooks("YourBook.xls") '<<==== CHANGE
Set SH = WB1.Sheets("MASTER") '<<==== CHANGE

SH.Copy Befo=WB2.Sheets(3)
End Sub

The addition of "Change" here ...

SH.Copy Befo=WB2.Sheets(3) '<<==== CHANGE

... and all may be well in his two sheet Workbook.

(Yes, Kirk, I'm a sarcastic son of a spreadsheet, but you know I mean well)
:-)

Cheers,
DriverDB

"Norman Jones" wrote in
:

Hi Kirk,

I keep getting 'subscript out of range'.

On which line?

The message indicates that the relevant workbook or sheet name is not
found. This may be the result o a typing error or an inadvertently
added/deleted trailing space etc.



---
Regards,
Norman



"kirkm" wrote in message
...
On Tue, 3 Oct 2006 09:34:36 +0100, "Norman Jones"
wrote:

Hi Kirk,

Try something like:

'=============
Public Sub Tester002()
Dim WB1 As Workbook
Dim WB2 As Workbook
Dim SH As Worksheet

Set WB1 = Workbooks("Backup.xls") '<<==== CHANGE
Set WB2 = Workbooks("YourBook.xls") '<<==== CHANGE
Set SH = WB1.Sheets("MASTER") '<<==== CHANGE

SH.Copy Befo=WB2.Sheets(3)
End Sub
'<<=============

Hi Norman

I keep getting 'subscript out of range'.
Where should that code go - in a new .xls or the one I
want to import to?

I presume the xxx.xls string includes the full path ?
(Although I tried with and without).

Thanks - Kirk

Hi Driver,

The addition of "Change" here ...

SH.Copy Befo=WB2.Sheets(3) '<<==== CHANGE

... and all may be well in his two sheet Workbook.

Your hunch may very well be correct - especially as Kirk's needs derive
from the deletion of a worksheet.

However, a response ro the question:

On which line?

would be enlightening!


---
Regards,
Norman



"DriverDB" <DriverDB@home wrote in message
.. .
Hi Norman.

Obviously, it's all your fault. <grin

You held Kirks hand only three times out of four.

<snip
Set WB1 = Workbooks("Backup.xls") '<<==== CHANGE
Set WB2 = Workbooks("YourBook.xls") '<<==== CHANGE
Set SH = WB1.Sheets("MASTER") '<<==== CHANGE

SH.Copy Befo=WB2.Sheets(3)
End Sub

The addition of "Change" here ...

SH.Copy Befo=WB2.Sheets(3) '<<==== CHANGE

... and all may be well in his two sheet Workbook.

(Yes, Kirk, I'm a sarcastic son of a spreadsheet, but you know I mean
well)
:-)

Cheers,
DriverDB

"Norman Jones" wrote in
:

Hi Kirk,

I keep getting 'subscript out of range'.

On which line?

The message indicates that the relevant workbook or sheet name is not
found. This may be the result o a typing error or an inadvertently
added/deleted trailing space etc.



---
Regards,
Norman



"kirkm" wrote in message
...
On Tue, 3 Oct 2006 09:34:36 +0100, "Norman Jones"
wrote:

Hi Kirk,

Try something like:

'=============
Public Sub Tester002()
Dim WB1 As Workbook
Dim WB2 As Workbook
Dim SH As Worksheet

Set WB1 = Workbooks("Backup.xls") '<<==== CHANGE
Set WB2 = Workbooks("YourBook.xls") '<<==== CHANGE
Set SH = WB1.Sheets("MASTER") '<<==== CHANGE

SH.Copy Befo=WB2.Sheets(3)
End Sub
'<<=============

Hi Norman

I keep getting 'subscript out of range'.
Where should that code go - in a new .xls or the one I
want to import to?

I presume the xxx.xls string includes the full path ?
(Although I tried with and without).

Thanks - Kirk

Hi Guys,

Thanks for the help.

Norman, I put your code in Module1 in my "v6-20061001.xls", changing
it thus:

Public Sub Tester002()
Dim WB1 As Workbook
Dim WB2 As Workbook
Dim SH As Worksheet

Set WB1 = Workbooks("E:\Work\New\Backup\Backup.xls") '<<====
CHANGE
Set WB2 = Workbooks("E:\Work\New\v6-20061001.xls") '<<====
CHANGE
Set SH = WB1.Sheets("Sheet4") '<<==== CHANGE

SH.Copy Befo=WB2.Sheets(3)
End Sub

When I run it now, on Line 'Set WB1 = ....', I get

Can't move focus to the control because it is invisible, not enabled,
or of a type that does not accept the focus.

However, on the next (and subsequent) runs the error message changes
to 'Subscript out of range'. On the same line.

The change suggested by DriverDB, execution didn't reach that far to
see what happens. There's 4 sheets in the workbook and it's
the 4th one I need back. I assume, at worst, it might copy the
'wrong' sheet and I should be able to fix that ?

Many thanks - Kirk

I didn't read the whole thread, but this line:

Set WB1 = Workbooks("E:\Work\New\Backup\Backup.xls")
doesn't want any of that drive\path stuff.
Set WB1 = Workbooks("Backup.xls")
And Backup.xls has to be open already.

Same thing with v6-20061001.xls, too.

kirkm wrote:

Hi Guys,

Thanks for the help.

Norman, I put your code in Module1 in my "v6-20061001.xls", changing
it thus:

Public Sub Tester002()
Dim WB1 As Workbook
Dim WB2 As Workbook
Dim SH As Worksheet

Set WB1 = Workbooks("E:\Work\New\Backup\Backup.xls") '<<====
CHANGE
Set WB2 = Workbooks("E:\Work\New\v6-20061001.xls") '<<====
CHANGE
Set SH = WB1.Sheets("Sheet4") '<<==== CHANGE

SH.Copy Befo=WB2.Sheets(3)
End Sub

When I run it now, on Line 'Set WB1 = ....', I get

Can't move focus to the control because it is invisible, not enabled,
or of a type that does not accept the focus.

However, on the next (and subsequent) runs the error message changes
to 'Subscript out of range'. On the same line.

The change suggested by DriverDB, execution didn't reach that far to
see what happens. There's 4 sheets in the workbook and it's
the 4th one I need back. I assume, at worst, it might copy the
'wrong' sheet and I should be able to fix that ?

Many thanks - Kirk

--

Dave Peterson

On Tue, 03 Oct 2006 21:13:19 -0500, Dave Peterson
wrote:

I didn't read the whole thread, but this line:

Set WB1 = Workbooks("E:\Work\New\Backup\Backup.xls")
doesn't want any of that drive\path stuff.
Set WB1 = Workbooks("Backup.xls")
And Backup.xls has to be open already.

Same thing with v6-20061001.xls, too.

That did it !

Thanks one & all
Much appreciated.


Cheers - Kirk