importing worksheet via code
 

How do I import a worksheet from an existing .xls file to a spreadsheet that
is currently open and active?

importing worksheet via code
 

Hi Willie,

If both workbooks are open, then try:

'========================
Sub Tester04()
Dim destWB As Workbook
Dim srcWB As Workbook

Dim sh As Worksheet

Set destWB = ActiveWorkbook
Set srcWB = Workbooks("TEST1.xls")'<<===== CHANGE

Set sh = srcWB.Sheets("sheet1") '<<===== CHANGE

Application.ScreenUpdating = False

With destWB
sh.Copy after:=.Sheets(.Sheets.Count)
End With

Application.ScreenUpdating = True

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


If the source workbook is closed. try:

'========================
Sub Tester05()

Dim destWB As Workbook
Dim srcWB As Workbook
Dim MyPath As String
Dim sName As String
Dim sh As Worksheet

Set destWB = ActiveWorkbook

MyPath = _
"C:\Documents and Settings\User\My Documents" '<<= CHANGE

sName = "TEST1.xls" '<<===== CHANGE

Set srcWB = Workbooks.Open(MyPath & "\" & sName)

Set sh = srcWB.Sheets("sheet1") '<<===== CHANGE

Application.ScreenUpdating = False

With destWB
sh.Copy after:=.Sheets(.Sheets.Count)
End With
srcWB.Close (False)

Application.ScreenUpdating = True

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

Change the source workbook name , the sheet name and the path as
appropriate.

---
Regards,
Norman



"Willie Smith" wrote in message
om...
How do I import a worksheet from an existing .xls file to a spreadsheet
that is currently open and active?

importing worksheet via code
 

Thanks, just what I wanted
"Norman Jones" wrote in message
...
Hi Willie,

If both workbooks are open, then try:

'========================
Sub Tester04()
Dim destWB As Workbook
Dim srcWB As Workbook

Dim sh As Worksheet

Set destWB = ActiveWorkbook
Set srcWB = Workbooks("TEST1.xls")'<<===== CHANGE

Set sh = srcWB.Sheets("sheet1") '<<===== CHANGE

Application.ScreenUpdating = False

With destWB
sh.Copy after:=.Sheets(.Sheets.Count)
End With

Application.ScreenUpdating = True

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


If the source workbook is closed. try:

'========================
Sub Tester05()

Dim destWB As Workbook
Dim srcWB As Workbook
Dim MyPath As String
Dim sName As String
Dim sh As Worksheet

Set destWB = ActiveWorkbook

MyPath = _
"C:\Documents and Settings\User\My Documents" '<<= CHANGE

sName = "TEST1.xls" '<<===== CHANGE

Set srcWB = Workbooks.Open(MyPath & "\" & sName)

Set sh = srcWB.Sheets("sheet1") '<<===== CHANGE

Application.ScreenUpdating = False

With destWB
sh.Copy after:=.Sheets(.Sheets.Count)
End With
srcWB.Close (False)

Application.ScreenUpdating = True

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

Change the source workbook name , the sheet name and the path as
appropriate.

---
Regards,
Norman



"Willie Smith" wrote in message
om...
How do I import a worksheet from an existing .xls file to a spreadsheet
that is currently open and active?