PDA

View Full Version : [SOLVED] Run-Time error 9 Subscript out of range



Kirkos
01-29-2019, 03:19 AM
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
01-29-2019, 06:38 AM
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
01-29-2019, 06:42 AM
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
01-29-2019, 06:54 AM
When the code fails have a look to see how many sheets you have in the new workbook & what their names are.

Kirkos
01-29-2019, 07:35 AM
I get a workbook only with GER.
AUT still fails. Here is my list of sheets.
When I use Tabelle1 i get the same

23658



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
01-29-2019, 07:53 AM
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
01-30-2019, 01:30 AM
Hey Fluff

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

Fluff
01-30-2019, 05:50 AM
You're welcome & thanks for the feedback