VBa Help - For Each & .Printout Code failing on my List of Sheets

[Johnny Thunder]

Hello All,

I am working on a code that I have parsed from several other projects and have hit a small roadblock.

Here is what I got.

Description of my code

1. Does a loop (For Each Statement #1 ) loops thru a list of Filenames on my worksheet Column A and and sets the filename as a variable and then opens the file

2. Once the file is open my second Loop (For Each Statement #2 ) loops thru a list of Sheetnames in a named range "SheetList" and if the adjacent cell of the sheetname contains the text True then it will define a variable "NameofSheet" as a variable and apply .PrintOut to the NameofSheet variable.

Where I am at with the code

1. Currently, when I run the code, the code will run thru the first file and printout the first sheet from the variable "NameOfSheet" as soon as the loop goes to the next Sheetname to print out I get an error "Runtime Error:9 Subscript out of Range"

I have confirmed that the sheetnames I am attempting to print exist. I have also added a msgbox NameofSheet to validate that the code is in fact looping to a secondsheet after the first successful print to see what the second NameofSheet is, but I am not able to figure out what the underlying issue is.

Any help in troubleshooting is appreciated.

Sub PrintSheets()
'--------------------------------------------------------
'--- Prints Only specific Sheets from each Territory
'---------------------------------------------------------
Dim Cell            As Range, Cell2 As Range
Dim File            As Variant
Dim folder          As Object
Dim Rng             As Range
Dim RngEnd          As Range
Dim Wks             As Worksheet
Dim NameOfSheet     As String
Dim OpenWB          As Workbook
    
Set Wks = Sheet2


Set Rng = Wks.Range("A2")
Set RngEnd = Wks.Cells(Rows.cOunt, Rng.Column).End(xlUp)


Application.DisplayAlerts = False
Application.ScreenUpdating = False




    '---------------------------Beginning of code-------------------------------------
    If RngEnd.Row < Rng.Row Then Exit Sub Else Set Rng = Wks.Range(Rng, RngEnd)
      
    With CreateObject("Shell.Application")
        For Each Cell In Rng
            If Cell.Offset(0, 1).Value = Empty Then
                Set folder = .Namespace("C:\Users\JLLEE\Dowloaded Files") '<-----------Folder where to find files
                  
                '-------------Check that the folder exists--------------
                If Not folder Is Nothing Then
                    '---------- Return the file name with the extension.
                    File = Dir(folder.self.Path & "\" & Cell.Value & "*")
                
                    '---------Check the file exists---------------------
                    If File <> "" Then
                        File = folder.self.Path & "\" & File
                        Cell.Offset(0, 1).Value = Now()
                    
    Set OpenWB = Workbooks.Open(File) '----------Opens each individual file
                    
              For Each Cell2 In ThisWorkbook.Sheets("PrintTBTFiles").Range("SheetList") 'Named Range on Sheet2
                    If Cell2.Offset(, 1).Value = True Then
                        NameOfSheet = Cell2.Value 'Defines the sheet name
                        'MsgBox NameOfSheet <--------------My line to troubleshoot
                        Worksheets(NameOfSheet).PrintOut 'Prints each sheet '<-----------------------This is the line that errors out
                        OpenWB.Close SaveChanges:=False 'Closes the workbook
                        End If
                     Next
                End If
            End If
        End If
    Next Cell
End With


MsgBox "All Files/Sheets have been printed"


Application.DisplayAlerts = True
Application.ScreenUpdating = True


End Sub

 

[Fluff]

With that error, it's almost certainly a problem with the sheet name. Check for spelling, leading/trailing spaces.

 

[Johnny Thunder]

Thanks for the response @Fluff.

So here is what I have done since yesterday with troubleshooting.

I have validated that my list of Sheetnames do not have any misspellings. I have also validated that the workbooks that I am opening have the specific sheets I am testing to ensure I am not trying to print something that doesn't exist.

So here is something funny that I am noticing - When I run the code to print two sheets (Sheets "Exec Summary" & Sheets"FY1") the code will print all of the Sheets("Exec Summary") and then error out as it redefines the variable NameofSheet to Sheets("FY1") like it can't find it? And here is the weirdness, If I update the list to only print out Sheets("FY1") it loops thru the code with absolutely no issue and prints/finds the sheets in my list of workbooks?

I am so confused on what the issue could be, any ideas? Possibly an update to my code to improve upon how it is doing what I have coded?

With that error, it's almost certainly a problem with the sheet name. Check for spelling, leading/trailing spaces.

 

[Fluff]

How about

[COLOR=#ff0000]OpenWB.[/COLOR]Worksheets(NameOfSheet).PrintOut 'Prints each sheet

 

[Johnny Thunder]

Fluff, what would I modify to enter this proposed change? Is it possible to copy the code and place this revision within it?

Thanks

 

[Fluff]

Just add the part in red to the existing line of code ( I've edited the previous post to suit)

 

[Johnny Thunder]

Thanks for the clarification.

So now that I have added the revision you suggested, I am now receiving a RunTime Error: 2147221080 (800401a8) Automation Error on the revised line.

Revised code

Sub PrintSheets()
'--------------------------------------------------------
'--- Prints Only specific Sheets from each Territory
'---------------------------------------------------------
Dim Cell            As Range, Cell2 As Range
Dim File            As Variant
Dim folder          As Object
Dim Rng             As Range
Dim RngEnd          As Range
Dim Wks             As Worksheet
Dim NameOfSheet     As String
Dim OpenWB          As Workbook
    
Set Wks = Sheet2


Set Rng = Wks.Range("A2")
Set RngEnd = Wks.Cells(Rows.cOunt, Rng.Column).End(xlUp)


Application.DisplayAlerts = False
Application.ScreenUpdating = False




    '---------------------------Beginning of code-------------------------------------
    If RngEnd.Row < Rng.Row Then Exit Sub Else Set Rng = Wks.Range(Rng, RngEnd)
      
    With CreateObject("Shell.Application")
        For Each Cell In Rng
            If Cell.Offset(0, 1).Value = Empty Then
                Set folder = .Namespace("C:\Users\JLLEE\Dowloaded Files") '<-----------Folder where to find files
                  
                '-------------Check that the folder exists--------------
                If Not folder Is Nothing Then
                    '---------- Return the file name with the extension.
                    File = Dir(folder.self.Path & "\" & Cell.Value & "*")
                
                    '---------Check the file exists---------------------
                    If File <> "" Then
                        File = folder.self.Path & "\" & File
                        Cell.Offset(0, 1).Value = Now()
                    
    Set OpenWB = Workbooks.Open(File) '----------Opens each individual file
                    
              For Each Cell2 In ThisWorkbook.Sheets("PrintTBTFiles").Range("SheetList") 'Named Range on Sheet2
                    If Cell2.Offset(, 1).Value = True Then
                        NameOfSheet = Cell2.Value 'Defines the sheet name
                            OpenWB.Worksheets(NameOfSheet).PrintOut 'Prints each sheet
                          
                          OpenWB.Close SaveChanges:=False 'Closes the workbook
                        End If
                     Next
                End If
            End If
        End If
    Next Cell
End With


MsgBox "All Files/Sheets have been printed"


Application.DisplayAlerts = True
Application.ScreenUpdating = True


End Sub

 

[Fluff]

Does the workbook open?

 

[Johnny Thunder]

Yes it does.

So I think I may have just spotted the issue. My Openwb.close line is actually within the loop of going thru each sheet so after it prints the first sheet in my list it ends up closing the workbook and then redifining the NameofSheet Variable to the next sheet in my list and now it try's to print on a workbook that is no longer open. I am going to try and move the .close command to the End If statement after the Next Statement and see if that solves it.

Thanks again for all your help.