Import Worksheet

[kirkm]

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

 

[Norman Jones]

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 Before:=WB2.Sheets(3)
End Sub
'<<=============

 

[Jim Cone]

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"
<[email protected]>
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

 

[kirkm]

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 Before:=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

 

[kirkm]

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

Cheers - Kirk

 

[Norman Jones]

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.

 

[DriverDB]

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 Before:=WB2.Sheets(3)
End Sub

The addition of "Change" here ...

SH.Copy Before:=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]

Hi Driver,

The addition of "Change" here ...

SH.Copy Before:=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:

would be enlightening!

 

[kirkm]

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 Before:=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]

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]

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