Copying worksheets from one workbook to another based on a list

[ath]

Hello,

I am trying to get some code working where I can copy worksheets from one workbook into another based on a list. Here is the code i have put together so far:

Dim Sht As Worksheet
Dim sheet_names As Variant
Dim destWB As Workbook
Set destWB = ThisWorkbook

sheet_names = OpenBook.Sheets("CaseList").Range("C2:C2000").Value

For Each Sht In OpenBook.Sheets

If Not IsError(Application.Match(Sht.Name, sheet_names, 0)) Then

Sht.Copy After:=destWB.Sheets(Sheets.Count)

End If

Next Sht

When i run the code, i get a runtime error 424 message. Any help would be appreciated.

Thanks

 

[JLGWhiz]

I think I would do it like this, Assuming OpenBook has been declared and initialized elsewhere in the code.

Dim Sht As Worksheet
Dim sheet_names As Range
Dim destWB As Workbook
Set destWB = ThisWorkbook
Set sheet_names = OpenBook.Sheets("CaseList").Range("C2:C2000")
For Each Sht In OpenBook.Sheets
    If Application.CountIf(sheet_names, Sht.Name) = 0 Then 'This might need tweaking  to change from = to >
        Sht.Copy After:=destWB.Sheets(Sheets.Count)
   End If
Next Sht

 

[ath]

thanks JLGWhiz for responding. this is what i have now, based on your recommendations and i tried it with = and > and i am getting a runtime error 9 message:

Dim FileToOpen As Variant
Dim OpenBook As Workbook
Dim destWB As Workbook
Set destWB = ThisWorkbook
Dim Sht As Worksheet
Dim sheet_names As Range

Application.ScreenUpdating = False

FileToOpen = Application.GetOpenFilename(Title:="Browse for your File & Import Range", FileFilter:="Excel Files (*.xls*),*xls*")

If FileToOpen <> False Then

Set OpenBook = Application.Workbooks.Open(FileToOpen)

End If

Set sheet_names = OpenBook.Sheets("CaseList").Range("C2:C2000")

For Each Sht In OpenBook.Sheets

If Application.CountIf(sheet_names, Sht.Name) > 0 Then 'This might need tweaking to change from = to >

Sht.Copy After:=destWB.Sheets(Sheets.Count)

End If

Next Sht

 

[JLGWhiz]

Well, the equal and greater than symbols have nothing to do with the 'Subscript out of Range' message, That error occurs when the code is telling the compiler to perform an execution on an object that does not exist in the file the code points to. It can be misspelled file or sheet names, spaces that are leading, trailing or added between strings in a name, and ommission of spaces between strings in a name. In simple terms, what the code tells the compiler to look for must match what is on the worksheet where it is told to look.

I mentioned before that I did not see any reference to 'OpenBook' and assumed you had it defined elsewhere in the code. If you do not have that variable declared and 'Set' prior to a statement telling it to execute on that variable, then it will give you the Error 9.

 

[JLGWhiz]

This ran without error for me in a test set up.

Dim FileToOpen As Variant
Dim OpenBook As Workbook
Dim destWB As Workbook
Set destWB = ThisWorkbook
Dim Sht As Worksheet
Dim sheet_names As Range
Application.ScreenUpdating = False
FileToOpen = Application.GetOpenFilename(Title:="Browse for your File & Import Range", FileFilter:="Excel Files (*.xls*),*xls*")
    If FileToOpen <> False Then
        Set OpenBook = Workbooks.Open(FileToOpen)
    Else
        MsgBox "FileToOpen failed"
        Exit Sub
    End If
Set sheet_names = OpenBook.Sheets("CaseList").Range("C2:C2000")
For Each Sht In OpenBook.Sheets
    If Application.CountIf(sheet_names, Sht.Name) > 0 Then 'This might need tweaking to change from = to >
        Sht.Copy After:=destWB.Sheets(Sheets.Count)
    End If
Next Sht

Regarding the = and > symbols, It depends on what condition you want. I don't know what the names in column C of sheet "CaseList" represents. So if you want to use the list to prevent duplication the = 0 would apply, but if you are testing for applicability of sorts then the > 0 would apply.

 

[ath]

Thanks for the reply. In my second post, i included everything how i am running the code, including setting openbook.

If i take out Sht.Copy After:=destWB.Sheets(Sheets.Count) and replace it with Sht.Protect, the code will loop through all worksheets in the openbook and protects them. that leads me to believe that it is in Sht.Copy After:=destWB.Sheets(Sheets.Count). My guess is that it doesn't like the destWB declaration. I've tried ThisWorkbook as well and get the same error.

 

[JLGWhiz]

The easy way to find out why you are getting an error is to click the 'Debug' button and it will show you the line of code where execution failed.

 

[ath]

When i run debug, it says the this is the line of code where execution failed is: Sht.Copy After:=destWB.Sheets(Sheets.Count)

I took the code you tested and ran it in my workbooks and still get the runtime 9 error. I set up two test workbooks, one to run it from and the other to copy from and i still get the runtime error 9.

I really can't see what is going wrong. Thanks for your help.

 

[ath]

I was able to get this to work. Main thing was adding destWB to (Sheets.Count) cause otherwise it was counting the sheets in the wrong workbook. Thanks for all of your help.

Dim FileToOpen As Variant
Dim OpenBook As Workbook
Dim destWB As Workbook
Set destWB = ThisWorkbook
Dim Sht As Worksheet
Dim sheet_names As Range

FileToOpen = Application.GetOpenFilename(Title:="Browse for your File & Import Range", FileFilter:="Excel Files (*.xls*),*xls*")

If FileToOpen <> False Then

Set OpenBook = Application.Workbooks.Open(FileToOpen)

Set sheet_names = OpenBook.Sheets("CaseList").Range("C2:C2000")

End If

'loop through worksheets

For Each Sht In OpenBook.Sheets

If Not IsError(Application.Match(Sht.Name, sheet_names, 0)) Then

Sht.Copy After:=destWB.Sheets(destWB.Sheets.Count)

End If

Next Sht

 

[JLGWhiz]

glad you figured it out. I was running the code from the destination workbook, so the default did not throw the error. So it depends on where the code is run from and if that is the active workbook. Any object that is not qualified with its parent object, reverts to the active sheet. So we have to be sure all objects are properly qualified in the code. You can check your own code as the solution.
Regards, JLG