Solved: Copy sheet from closed workbook

[lucas]

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.

[VBA]
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
[/VBA]

[XLGibbs]

You arent specifying which workbook the Sales sheet is in....so it is looking in the ActiveWorkbook, which no longer has the sheet...that is my guess.

Set Wkb = Workbooks.Open(FileName:=path & "\" & FileName)
'copies Sales sheet in the bookkeeping.xls as the last sheet
Wkb.Sheets("Sales").Copy After:=Workbooks("Bookkeeping.xls").Sheets(ThisWorkbook.Sheets.Count)

[Zack Barresse]

Hey Steve,

I would add some more checks and balances in there. There is some areas where this is not done in the example I typed up, but I think you'll get the jist ..

[vba]Option Explicit

Sub ImportSalesSheet()

Dim Wkb As Workbook, BookKeep As Workbook
Dim strPath As String
Dim FileName As String
Dim ThisWB As String, strFullName As String
Dim Performed As Boolean, IsOpen As Boolean

Application.EnableEvents = False
Application.ScreenUpdating = False
Application.DisplayAlerts = False

Set BookKeep = Workbooks("Bookkeeping.xls")

Sheets("Sales").Select
ActiveWindow.SelectedSheets.Delete

ThisWB = ThisWorkbook.Name
strPath = ThisWorkbook.Path
'strPath = "C:\Documents\Test\"
FileName = "Invoice.xls"
strFullName = strPath & "\" & FileName
If IsWbOpen(FileName) Then
Set Wkb = Workbooks(FileName)
IsOpen = True
Else
Set Wkb = Workbooks.Open(strFullName)
IsOpen = False
End If

Wkb.Sheets("Sales").Copy After:=BookKeep.Sheets(ThisWorkbook.Sheets.Count)
' Sheets("Sales").Copy After:=BookKeep.Sheets("Home Office Expenses")
If Not IsOpen Then 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

Function IsWbOpen(wbName As String) As Boolean
On Error Resume Next
IsWbOpen = Len(Workbooks(wbName).Name)
End Function

Function WsExists(wsName As String, Optional wb As Workbook)
If ActiveWorkbook Is Nothing Then Exit Function
If wb Is Nothing Then Set wb = ActiveWorkbook
On Error Resume Next
WsExists = Len(wb.Sheets(wsName).Name)
End Function[/vba]

[lucas]

Thanks for the insights(Pete, Zack) but I'm still having problems...will continue looking for a solution as I had already submitted this to the kb before I found the problem. Have requested it be moved back to work in progress until I can get it sorted out.

Pete, I think you are on the right track but haven't found a way to fix it yet. Zack, I agree there needs to be a check that the sheet exists or user must understand that it must exist in both books.

[Zack Barresse]

There are some assumptions in the code ...

[vba]Workbooks("Bookkeeping.xls")

Sheets("Sales").Select

BookKeep.Sheets(ThisWorkbook.Sheets.Count)[/vba]

Each one of those lines has some major assumptions. The largest being the last line. If the BookKeeping.xls file does not have as many sheets in it as ThisWorkbook does, it will error out there. To get a better idea where this is going wrong, try stepping through your code. Where does the error produce and what is the error?

[lucas]

The first error I am getting with your code Zack is on this line:
[VBA] Set BookKeep = Workbooks("Bookkeeping.xls")[/VBA]
Error is "Subscript out of Range"

[Zack Barresse]

So that means it's either spelled wrong or that book isn't open. You'd need to perform a check on it like we did with the other one with the Function.

[lucas]

I feel like a dumba** now Zack. I had renamed it to "Run this file.xls" for the kb. Seems to be working now but will test it for a couple of days in different locations. May I add your code to the entry?

[Zack Barresse]

Of course, no need to ask me that. What's mine is yours, my friend.