Simple VBA Question

  • Thread starter Thread starter Justin
  • Start date Start date
J

Justin

I'm new at VBA, so this should be an easy question for
you. What VBA method/statement would I use to bring data
from one excel spreadsheet to another.

Example:
I have a worksheet (C:\ TempReport.xls)

On the open event of the current worksheet I want to copy
the entire worksheet in TempReport and bring it over to my
current worksheet.

I know how to do this on by creating a macro, but is there
a way to do this without opening the user interface and
copying and pasting? A way that does it all in the
background?

Thanks.
 
One way:

Private Sub Workbook_Open()
Const SOURCEBK As String = "C:\TempReport.xls"
Dim tempBk As Workbook
Application.ScreenUpdating = False
With ThisWorkbook
Set tempBk = Workbooks.Open(SOURCEBK)
tempBk.Sheets(1).Copy After:=.Sheets(.Sheets.Count)
tempBk.Close SaveChanges:=False
End With
Application.ScreenUpdating = True
End Sub

Put this in the ThisWorkbook code module (right-click on the
workbook title bar, choose View Code, paste the following in the
window that opens, then click the XL icon on the toolbar to return
to XL)

Note: This assumes that TempReport.xls has only one worksheet,
therefore Sheets(1) is copied. Adjust to suit.
 
Thanks
-----Original Message-----
One way:

Private Sub Workbook_Open()
Const SOURCEBK As String = "C:\TempReport.xls"
Dim tempBk As Workbook
Application.ScreenUpdating = False
With ThisWorkbook
Set tempBk = Workbooks.Open(SOURCEBK)
tempBk.Sheets(1).Copy After:=.Sheets (.Sheets.Count)
tempBk.Close SaveChanges:=False
End With
Application.ScreenUpdating = True
End Sub

Put this in the ThisWorkbook code module (right-click on the
workbook title bar, choose View Code, paste the following in the
window that opens, then click the XL icon on the toolbar to return
to XL)

Note: This assumes that TempReport.xls has only one worksheet,
therefore Sheets(1) is copied. Adjust to suit.


.
Click to expand...
 
Back
Top