Run-Time error 9 Subscript out of range

[Kirkos]

Dear all

I am using a code for long time ago which extracts a tab of an excel workfile to a seperate excel file. (Office Professional Plus 2010 Version 14.0.7184.5000 32bit). However i faced this issue in the past many times and always was solved with trial and error. However i would like you to help to understand the cause.

I tried to use the same code in the Excel version (Office 365 MSO 16.0.11126.20192 64bit)
So here is the error when i debug:

Error Message : "Run-time error 9 Subscript out of range"

Sub Abrechnungs_Statistik_erstellen()

Application.ScreenUpdating = False


d = Sheets("Abrechnung").Cells(9, 13).Value




Sheets("AUT").Select
a = Range(Cells(1, 1), Cells(100000, 23)).Value
aa = Range(Cells(1, 23), Cells(100000, 34)).Value


'Sheets("CHE").Select
'b = Range(Cells(1, 1), Cells(100000, 1)).Value
'bb = Range(Cells(1, 7), Cells(100000, 23)).Value


Sheets("GER").Select
c = Range(Cells(1, 1), Cells(100000, 23)).Value
cc = Range(Cells(1, 23), Cells(100000, 34)).Value


'Sheets("Sheet3").Select
'Sheets("Sheet3").Name = "AUT"
'Range(Cells(1, 1), Cells(100000, 1)).Value = a
'Range(Cells(1, 2), Cells(100000, 18)).Value = aa


'Sheets("Tabelle2").Select
'Sheets("Tabelle2").Name = "CHE"
'Range(Cells(1, 1), Cells(100000, 1)).Value = b
'Range(Cells(1, 2), Cells(100000, 18)).Value = bb


Dim Abrechnungs_Statistik As Workbook
Set Abrechnungs_Statistik = Workbooks.Add


Sheets("Sheet1").Select
Sheets("Sheet1").Name = "GER"
Range(Cells(1, 1), Cells(100000, 23)).Value = c
Range(Cells(1, 23), Cells(100000, 34)).Value = cc


Sheets("Tabelle1").Select
Sheets("Tabelle1").Name = "AUT"
Range(Cells(1, 1), Cells(100000, 23)).Value = a
Range(Cells(1, 23), Cells(100000, 34)).Value = aa

The problem is in the red letters. When i rename the sheet as Sheet2 the problem remains. Even when i use the name of the tab. (AUT)

Do you have any thoughts about it?

Thank you in advance for your kind responses.

Best
G

(P.S i am sorry if i violated any rule. I am learning)

[Fluff]

That error means that you don't have a sheet called "Tabelle1"
Also you are trying to name the sheet "AUT", but you already seem to have a sheet of that name.

[Kirkos]

Hi Fluff

Thank you for your reply. I got the same but how do I know what is the name of the sheet? I tried all the alternatives but it didnt work.
Yes i try to name the sheet AUT also in the new file. I will try to change that.

[Fluff]

When the code fails have a look to see how many sheets you have in the new workbook & what their names are.

[Kirkos]

I get a workbook only with GER.
AUT still fails. Here is my list of sheets.
When I use Tabelle1 i get the same

Example.PNG

Sub Abrechnungs_Statistik_erstellen()

Application.ScreenUpdating = False


d = Sheets("Abrechnung").Cells(9, 13).Value




Sheets("AUT").Select
a = Range(Cells(1, 1), Cells(100000, 23)).Value
aa = Range(Cells(1, 23), Cells(100000, 34)).Value


'Sheets("CHE").Select
'b = Range(Cells(1, 1), Cells(100000, 1)).Value
'bb = Range(Cells(1, 7), Cells(100000, 23)).Value


Sheets("GER").Select
c = Range(Cells(1, 1), Cells(100000, 23)).Value
cc = Range(Cells(1, 23), Cells(100000, 34)).Value


'Sheets("Sheet3").Select
'Sheets("Sheet3").Name = "AUT"
'Range(Cells(1, 1), Cells(100000, 1)).Value = a
'Range(Cells(1, 2), Cells(100000, 18)).Value = aa


'Sheets("Tabelle2").Select
'Sheets("Tabelle2").Name = "CHE"
'Range(Cells(1, 1), Cells(100000, 1)).Value = b
'Range(Cells(1, 2), Cells(100000, 18)).Value = bb


Dim Abrechnungs_Statistik As Workbook
Set Abrechnungs_Statistik = Workbooks.Add


Sheets("Sheet1").Select
Sheets("Sheet1").Name = "GER"
Range(Cells(1, 1), Cells(100000, 23)).Value = c
Range(Cells(1, 23), Cells(100000, 34)).Value = cc


Sheets("Tabelle1").Select
Sheets("Tabelle1").Name = "AUT"
Range(Cells(1, 1), Cells(100000, 23)).Value = a
Range(Cells(1, 23), Cells(100000, 34)).Value = aa

[Fluff]

Those sheet names are in the workbook containing the code.
If you only have 1 sheet in the new workbook, then you will need to add more sheets, like

Sheets.add.name="AUT"

[Kirkos]

Hey Fluff

thanks a lot. It seems that add.name solved the problem.

[Fluff]

You're welcome & thanks for the feedback