lucas
02-07-2006, 04:50 PM
I'm trying to delete the "Sales" sheet in the bookkeeping.xls and then copy the "Sales" sheet from the invoice.xls
It works to begin with but when I come back to it the next day I get a Script out of range error on the red line...any suggestions.
Sub ImportSalesSheet()
Dim path As String
Dim FileName As String
Dim Wkb As Workbook
Dim ThisWB As String
Application.EnableEvents = False
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Sheets("Sales").Select
ActiveWindow.SelectedSheets.Delete
ThisWB = ThisWorkbook.Name
'Use this workbook path
path = ThisWorkbook.path
'Uncomment the next line to hard code the path
'path = "C:\Documents\Test\"
'the name of the file you wish to copy the Sales sheet from
FileName = "Invoice.xls"
Set Wkb = Workbooks.Open(FileName:=path & "\" & FileName)
'copies Sales sheet in the bookkeeping.xls as the last sheet
Sheets("Sales").Copy After:=Workbooks("Bookkeeping.xls").Sheets(ThisWorkbook.Sheets.Count)
'to copy sheet to specific location use line below
'set to copy the sales sheet after the "Home Office Expenses" sheet
' Sheets("Sales").Copy After:=Workbooks("Bookkeeping.xls").Sheets("Home Office Expenses")
Wkb.Close False
MsgBox "Latest version of Sales Sheet successfully copied to this workbook.", vbInformation
Application.EnableEvents = True
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
It works to begin with but when I come back to it the next day I get a Script out of range error on the red line...any suggestions.
Sub ImportSalesSheet()
Dim path As String
Dim FileName As String
Dim Wkb As Workbook
Dim ThisWB As String
Application.EnableEvents = False
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Sheets("Sales").Select
ActiveWindow.SelectedSheets.Delete
ThisWB = ThisWorkbook.Name
'Use this workbook path
path = ThisWorkbook.path
'Uncomment the next line to hard code the path
'path = "C:\Documents\Test\"
'the name of the file you wish to copy the Sales sheet from
FileName = "Invoice.xls"
Set Wkb = Workbooks.Open(FileName:=path & "\" & FileName)
'copies Sales sheet in the bookkeeping.xls as the last sheet
Sheets("Sales").Copy After:=Workbooks("Bookkeeping.xls").Sheets(ThisWorkbook.Sheets.Count)
'to copy sheet to specific location use line below
'set to copy the sales sheet after the "Home Office Expenses" sheet
' Sheets("Sales").Copy After:=Workbooks("Bookkeeping.xls").Sheets("Home Office Expenses")
Wkb.Close False
MsgBox "Latest version of Sales Sheet successfully copied to this workbook.", vbInformation
Application.EnableEvents = True
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub