Subscript out of range inspite of the sheets still present there.

[JainAbraham]

Hi All,

I am trying to create a Macro for coping columns froms different sheets into one so for that i am assigning a variable to the worksheet where in i am getting an error of only one sheet the other sheets are assigned variables correctly.

i the below is my code i am not sure what i am missing because i have a worksheet named Rule Catalog yet it give me this error.

Option Explicit
Public Sub ResourceRelation()
    Dim strptfile As String
    Dim strASfile As String
    Dim strASptfile As String
    Dim strAssgin As String
    
    Dim wrbMnsht As Workbook
    Dim wrbASsht As Workbook
    
    Dim shtMnsht As Worksheet
    Dim shtASsht As Worksheet
    Dim shtTemp As Worksheet
    
    Dim rngAll As Range
    Dim rngName As Range
    Dim rngAssgin As Range
    
    Application.StatusBar = "Opening the file to validate"
    
    Sheet1.Activate
        
    strptfile = ActiveSheet.Range("A2").Value
    Set wrbMnsht = Workbooks.Open(Filename:=strptfile)
    wrbMnsht.Activate
    
     'Assigning variable to the Main sheet
    Set shtMnsht = wrbMnsht.Worksheets(1)
    Application.StatusBar = "Opening the common database sheet."
    shtMnsht.Activate
    
    
    Sheet1.Activate
    'Selecting the Allstate sheet
    strASptfile = ActiveSheet.Range("A4").Value
    Set wrbASsht = Workbooks.Open(Filename:=strASptfile)
    wrbASsht.Activate
    
    'Assigning variable to the A doc
    Set shtASsht = wrbASsht.Sheets("PRISM")
    Application.StatusBar = "Opening the All Timesheet Submission Report"
    shtASsht.Activate
    
    
   'Create a range containing the column after thelast Column of the sheet
    shtMnsht.Activate
    
    Set rngAll = shtMnsht.UsedRange
    rngAll.Select
    
    With rngAll
           Set rngName = .Offset(1, .Columns.Count).Resize(.Rows.Count - 1, 1)
    End With
    rngName.Select
        
        
    strAssgin = "NT-ID"
    Set rngAssgin = Utility.finder(shtMnsht, strAssgin)
  
        
        
     ThisWorkbook.Activate
     
    Dim shtContentPage As Worksheet
    
    [COLOR=#ff0000]Set shtContentPage = ThisWorkbook.Sheets("ContentPage")
[/COLOR]   [COLOR=#ff0000]Set shtTemp = ThisWorkbook.Sheets("Final")
[/COLOR]   shtTemp.Cells.ClearContents
    shtTemp.Cells.ClearFormats
        
    
End Sub

I get the same error for both line. Kinldly take a look and let me know what i am missing. i have checked the other posts but still cannot really decipher it.

 

[Andrew Poulsom]

Check the names of the sheets again. They may have leading or trailing spaces. And they must be in the workbook that contains your VBA code (ThisWorkbook).

 

[JainAbraham]

That was it! i cannot believe such a small error.

Thank you Andrew, you are always there to help