VBA to use variable as sheet name

Tanyaann1995

Board Regular
Joined
Mar 24, 2021
Messages
57
Office Version
  1. 2016
Platform
  1. Windows
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.

VBA Code:
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
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
2,445
Office Version
  1. 2013
Platform
  1. Windows
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:
VBA Code:
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
 
Solution

Tanyaann1995

Board Regular
Joined
Mar 24, 2021
Messages
57
Office Version
  1. 2016
Platform
  1. Windows
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:
VBA Code:
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. :)
 

Forum statistics

Threads
1,148,159
Messages
5,745,111
Members
423,924
Latest member
Gazzat

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top