Exporting data to new Workbook regardless of language settings in VBA

Question

I have a code that gathers data from the current workbook (multiple worksheets) and exports that data in a predefined manner to a new workbook.

Problem: I programmed my code in English, so I reference some output sheets by name (eg. Sheet1). However, I want this code to be usable in excel with other Language preferences, (eg. tabela1, tabelle1...). If I reference "Sheet1", when the user is in a different language setting excel, the new workbook created will have a sheet with a different name.

Question: How can I solve this situation?

Already tried: Instead of the original:

w2.Sheets("Sheet1").Range...

I tried using:

w2.Worksheets(1).Range...

But apparently that does not work (subscript out of range error)

Obs1: I also tried adding a new worksheet to the new workbook, with a specified name, and keep the code as the original, but this approach is not so elegant.

Obs2: The relevant part of the code is where it pastes the identifier, the date and the content array to the new sheet.

The code:

Function ArrayFiller(arr As Variant, arr0 As Variant, y As String, Optional ind As Boolean) As Variant
Dim lRow As Long, lColumn As Long
Dim w2 As Workbook
Dim w3 As Workbook
Dim d As Date, d1 As Long, d2 As Long
Dim CompArray() As Variant

Workbooks.Add
Set w2 = ActiveWorkbook

    For lRow = LBound(arr, 1) To UBound(arr, 1)
        For lColumn = LBound(arr, 2) + 1 To UBound(arr, 2)
            If arr(lRow, lColumn) <> "" And arr(lRow, lColumn - 1) = "" Then

                    If arr0(lRow, lColumn) <> "" And arr0(lRow, lColumn) <> "--" Then
                        arr(lRow, lColumn - 1) = arr0(lRow, lColumn)
                            w2.Worksheets(1).Cells(lColumn - 1, lRow).Interior.Color = RGB(255, 0, 0)

                    ElseIf arr0(lRow, lColumn) = "" Or arr0(lRow, lColumn) = "--" Then
                        arr(lRow, lColumn - 1) = arr(lRow, lColumn)
                            w2.Worksheets(1).Cells(lColumn - 1, lRow).Interior.Color = RGB(255, 0, 0)
                    End If
            End If
        Next
    Next

w2.Sheets("Sheet1").Range("A1").Resize(UBound(arr, 2), UBound(arr, 1)).Value = Application.WorksheetFunction.Transpose(arr)

Columns(2).EntireColumn.Delete
Rows(2).EntireRow.Delete

d = Application.WorksheetFunction.WorkDay(w2.Sheets("Sheet1").Range("A3"), -1)
w2.Sheets("Sheet1").Range("A2") = d

w2.SaveAs Filename:=ThisWorkbook.path & "\" & "Output" & y, FileFormat:=6

CompArray() = w2.Worksheets(1).UsedRange.Value

w2.Close True

d1 = UBound(CompArray, 1)
d2 = UBound(CompArray, 2)

If ind = True Then

    Workbooks.Add
    Set w3 = ActiveWorkbook

    For lRow = LBound(CompArray, 1) + 1 To UBound(CompArray, 1)
        For lColumn = LBound(CompArray, 2) + 1 To UBound(CompArray, 2)
            If CompArray(lRow, lColumn) <> "" And CompArray(lRow, lColumn) <> "--" Then
                w3.Worksheets(1).Cells(lRow, lColumn).Value = 1
            Else
                w3.Worksheets(1).Cells(lRow, lColumn).Value = 0
            End If
        Next
    Next

Columns(1).EntireColumn.Insert
Rows(1).EntireRow.Insert

w3.Sheets("Sheet1").Range("A2:A" & d1 + 1).Value = CompArray
w3.Sheets("Sheet1").Range("B1").Resize(1, d2).Value = CompArray

w3.SaveAs Filename:=ThisWorkbook.path & "\OutputComposite", FileFormat:=6

w3.Close True

Else
End If

End Function

Any ideas?

Answer 1

This works: Sheets(1).Range("A1").Value = 10

Edit: However, be careful when referencing to sheets in this manner, when users can alter the order of the Sheets