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)
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)