CODEWITHSUNDEEP
excelvbaworksheet
IlvarNourtan
IlvarNourtan

Reputation: 57

How to name a worksheet?

I have a file (F) that contains several workbooks, each workbook has the same format. I do a conditional sum on each of the workbook under column conditions. I want to put the output within another workbook that contains one worksheet per workbook looped (contained within F).

I cannot find the good strategy to change the worksheet name in function of the looped workbook' name.

Set Output_tot_n = Workbooks("Final_Output").Sheet_name.Range("B7")

I got

Error 438 "Object doesn't support this property or method"

The whole code:

Sub Proceed_Data()
    
    Dim FileSystemObj As Object
    Dim FolderObj As Object
    Dim fileobj As Object
    Dim Sheet_name As Worksheet
    Dim i, j, k  As Integer
    Dim wb As Workbook
    
    Set FileSystemObj = CreateObject("Scripting.FileSystemObject")
    Set FolderObj = FileSystemObj.GetFolder("C:\...\")
    
    For Each fileobj In FolderObj.Files
        Set wb = Workbooks.Open(fileobj.Path)
    
        Set Output_tot_n = Workbooks("Final_Output").Sheet_name.Range("B7")
    
        If wb.Name = "AAA_SEPT_2018" Then
            Sheet_name = Worksheets("AAA")
        End If
        If wb.Name = "BBB_SEPT_2018" Then
            Sheet_name = Worksheets("BBB")
        End If
        If wb.Name = "CCC_SEPT_2018" Then
            Sheet_name = Worksheets("CCC")
        End If
    
        ' conditional sum

        With wb.Sheets("REPORT")
            For i = 2 To .Cells(Rows.Count, 14).End(xlUp).Row
                If .Cells(i, "O").Value = "sept" Then
                    k = .Cells(i, "M").Value
                End If
                j = j + k
                k = 0
            Next i
        End With
        Output_tot_n = j
        j = 0
                            
        wb.Save
        wb.Close
         
    Next fileobj
End Sub

Upvotes: 0

Views: 110

Answers (2)

IlvarNourtan
IlvarNourtan

Reputation: 57

No it actually works. Thank you again for your answers.

the problem was just is important to put "AAA_SEPT_2018.xlsx"

Upvotes: 0

FunThomas
FunThomas

Reputation: 29652

Workbooks is a collection (part of the actual Application-object). A collection in VBA can be accessed either by index number (index starts at 1) or by name. The name of an open Workbook is the name including the extension, in your case probably either Final_Output.xlsx or Final_Output.xlsm.

Sheets and Worksheets are collections within a Workbook, again accessed via index or name (the difference is that Worksheets contains "real" spreadsheets while Sheets may also contain other sheet types, eg charts).

So in your case, you want to access a Range of a specific sheet of a specific workbook. The workbook has a fixed name, while the sheet name is stored in a variable. You can write for example 

dim sheetName as string, sheet as Worksheet, Output_tot_n as Range
sheetName = "AAA"  ' (put your logic here)
set sheet = Workbooks("Final_Output.xlsm").Worksheets(Sheet_name)
set Output_tot_n = sheet.Range("B7")

or put all together (depending on your needs)

set Output_tot_n = Workbooks("Final_Output.xlsm").Worksheets(Sheet_name).Range("B7")

Upvotes: 1

Related Questions