Subscript out of range exception with worksheets

[jayakrishna tota]

Hi,

I updated a VBA macro file to accommodate new changes.
After making the changes, it was throwing procedure too large error. So, I have divided the code into 2 procedures. But, while accessing the worksheets I'm getting subscript out of range error now in site_tab_uv_tech procedure. Please advice. I'm uploading the necessary files for your review.

Thank You...

 

[Marc L]

Hi !

Here is the Excel worksheet formulas forum !

See in VBA forum with a better explanation, post code between codetags
where error occurs …
​

 

[Deepak]

Hi,

You are facing the error in below line as said workbook wb_output didn't find!!

last_intv_fcst = Workbooks(wb_output).Worksheets(10).Cells(4, 2)

You are also facing issue due to large subroutine then use like as below..

With Workbooks(wb_output)
    .Worksheets(3).Cells(7, 6).Value = .Worksheets(10).Cells(4, 2).Value
End With

&
Like as !!

[A3:C3]=Array("CST","Lock Calls","Adj FC Calls")

 

[jayakrishna tota]

Thanks for the response Deepak. I'll make modifications to reduce the size of the procedure as you have suggested. But, I'm clueless about "subscript out of range" error. Why I'm getting this error

 

[Deepak]

As i said you are facing the issue due to unavailability of workbook wb_output

 

[jayakrishna tota]

As i said you are facing the issue due to unavailability of workbook wb_output

wb_output holds active work book name. It is throwing error only after executing some of the portion of the procedure.
Please see the below code.

OutFileName = "c:\work\att_uverse_blue_rpt_macro_output.XLS"
Workbooks.Open Filename:="c:\work\att_uverse_blue_template_v9.xls"
ActiveWorkbook.SaveAs Filename:=OutFileName, FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
Workbooks.Open Filename:=OutFileName
wb_output = ActiveWorkbook.Name

 

[Deepak]

oK. But same is not referred in "Sub site_tab_uv_tech()"

It should be like as Sub site_tab_uv_tech(wb_output As String)
& this would be

'''''''''''''''''''''''''''''''''''''''''''''''''''

site_tab_uv_tech wb_output

'''''''''''''''''''''''''''''''''''''''''''''''''''

There's a lot of things to be optimized in ur code. It seems that there are recorded.

& Change these as well..

OutFileName = "c:\work\att_uverse_blue_rpt_macro_output.XLS"
        'Workbooks.Open Filename:="\\muraw78\macro\att_uverse_blue_template_v9.xls"
        Workbooks.Open Filename:="c:\work\att_uverse_blue_template_v9.xls"
        ActiveWorkbook.SaveAs Filename:=OutFileName, FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
        ActiveWorkbook.Close 0
        Workbooks.Open Filename:=OutFileName

or

 OutFileName = "c:\work\att_uverse_blue_rpt_macro_output.XLS"
        'Workbooks.Open Filename:="\\muraw78\macro\att_uverse_blue_template_v9.xls"
        Workbooks.Open Filename:="c:\work\att_uverse_blue_template_v9.xls"
        ActiveWorkbook.SaveAs Filename:=OutFileName, FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
       
        'OutFileName = "c:\work\att_uverse_rpt_macro_output.XLS"
        'Wk.SaveAs Filename:=OutFileName
        'Workbooks.Open Filename:=OutFileName
        wb_output = ActiveWorkbook.Name
        Workbooks(wb_output).Worksheets("Sheet2").Activate
        'Workbooks(wb_output).Worksheets("Sheet2").Delete
        'Workbooks(wb_output).Worksheets("Sheet3").Delete
        wb_output = ActiveWorkbook.Name

 

[jayakrishna tota]

Hi Deepak,

I'm able to reduce the size of the procedure by using some of the tips you have provided. So, everything is working fine now. Thanks a lot for your support.

Yes. Most of the code is generated by recording macro. so, it has to be optimized. I hope going through this forum will help me in doing that.

Thanks again.