VBA to use variable as sheet name

[Tanyaann1995]

Hi,

I have below code that opens a file from a folder and references the values in the original workbook as the sheet names for the newly opened workbook. I'm not able to figure out the error in below code since it shows Run Time error 438: Object doesn't support this property or method and highlights the below line. Please advise.

Dim j As Integer
Dim a As String
Dim e As String
Dim g As String
Dim h As String

For j = 12 To 20
Dim FSO As Object, folder As Object, file As Object
Set FSO = CreateObject("Scripting.FileSystemObject")
Set folder = FSO.GetFolder(a)
 For Each file In folder.Files
        If Right(file, 5) = ".xlsx" Then
        Workbooks.Open file
        Else
        MsgBox ("No Datasheets found in Sizing folder")
        End If
    h = wb.Worksheets("Unmerged").Cells(j, 1).Value
   [COLOR=rgb(184, 49, 47)] c = file.Sheets(h).Range("E22").Value & " " & file.Sheets(h).Range("H22").Value & " " & file.Sheets(h).Range("K22").Value & " " & file.Sheets(h).Range("N22").Value & " X " & file.Sheets(h).Range("E23").Value & " " & file.Sheets(h).Range("H23").Value & " " & file.Sheets(h).Range("K23").Value & " " & file.Sheets(h).Range("N23").Value[/COLOR]
    d = file.Sheets(h).Range("AG39").Value & " " & file.Sheets(h).Range("AK37").Value
    e = "Refer attached data sheet for more details"
    wb.Worksheets("Emerson COMMERCIAL OFFER").Range("G" & k).Value = "MODEL: " & wb.Worksheets("Unmerged").Cells(j, 2).Value & c & "Set Pr. " & d & e
    Next file
 Next j

 

[GWteB]

You are mixing up two types of objects and you cannot interchange them at will. I'm talking about the File object (member of FSO) and the Workbook object (member of Excel.Application). A File object doesn't have a Sheet object as a member...

Your code should look something like this:

Dim Wb As Workbook                                  ' <<<<<<<<<<<<<

For j = 12 To 20
Dim FSO As Object, folder As Object, file As Object
Set FSO = CreateObject("Scripting.FileSystemObject")
Set folder = FSO.GetFolder(a)
 For Each file In folder.Files
        If Right(file, 5) = ".xlsx" Then
       
        Set Wb = Workbooks.Open(file)               ' <<<<<<<<<<<<<

    h = Wb.Worksheets("Unmerged").Cells(j, 1).Value
   
    With Wb.Sheets(h)
        c = .Range("E22").Value & " " & _
            .Range("H22").Value & " " & _
            .Range("K22").Value & " " & _
            .Range("N22").Value & " X " & _
            .Range("E23").Value & " " & _
            .Range("H23").Value & " " & _
            .Range("K23").Value & " " & _
            .Range("N23").Value
    End With
       
        Else
        MsgBox ("No Datasheets found in Sizing folder")
        End If

 

[Tanyaann1995]

You are mixing up two types of objects and you cannot interchange them at will. I'm talking about the File object (member of FSO) and the Workbook object (member of Excel.Application). A File object doesn't have a Sheet object as a member...

Your code should look something like this:

Dim Wb As Workbook                                  ' <<<<<<<<<<<<<

For j = 12 To 20
Dim FSO As Object, folder As Object, file As Object
Set FSO = CreateObject("Scripting.FileSystemObject")
Set folder = FSO.GetFolder(a)
 For Each file In folder.Files
        If Right(file, 5) = ".xlsx" Then
      
        Set Wb = Workbooks.Open(file)               ' <<<<<<<<<<<<<

    h = Wb.Worksheets("Unmerged").Cells(j, 1).Value
  
    With Wb.Sheets(h)
        c = .Range("E22").Value & " " & _
            .Range("H22").Value & " " & _
            .Range("K22").Value & " " & _
            .Range("N22").Value & " X " & _
            .Range("E23").Value & " " & _
            .Range("H23").Value & " " & _
            .Range("K23").Value & " " & _
            .Range("N23").Value
    End With
      
        Else
        MsgBox ("No Datasheets found in Sizing folder")
        End If

Thanks, this worked perfectly.