"Subscript out of range" when saving file.

[David Jenkins]

Hi there,

I recorded a macro to complete what I wanted the spreadsheet to do and then place it in the workbook_open event.

I can see that it creates and saves the new workbook, but then give me the error when it tries to activate it.

Sub Workbook_Open()

    'Create a new workbook
    Dim Wk As Workbook
    Set Wk = Workbooks.Add
    Application.DisplayAlerts = False   
 Wk.SaveAs Filename:="\\cpwplc.com\data\data_perfplan\MI Team\Reporting\KPI Grid\Saved Files\KPI_Grid.xls", FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False


    Cells.Select
    Range("B1").Activate
    Selection.Copy
    Windows("KPI_Grid.xls").Activate '******* errors here ***********
    Cells.Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    ActiveWindow.DisplayGridlines = False
    Sheets("Sheet1").Select
    Sheets("Sheet1").Name = "Weekly"
    Range("B1:E1").Select
    Windows("KPI Grid V5k1 - macro testing.xlsm").Activate
    Sheets("Monthly").Select
    Cells.Select
    Range("B1").Activate
    Application.CutCopyMode = False
    Selection.Copy
    Windows("KPI_Grid.xls").Activate
    Sheets("Sheet2").Select
    Cells.Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    ActiveWindow.DisplayGridlines = False
    Sheets("Sheet2").Select
    Sheets("Sheet2").Name = "Monthly"
    Range("B1:E1").Select
    Application.CutCopyMode = False
    ChDir "\\cpwplc.com\data\data_perfplan\MI Team\Reporting\KPI Grid\Saved Files"
    ActiveWorkbook.SaveAs Filename:= _
        "\\cpwplc.com\data\data_perfplan\MI Team\Reporting\KPI Grid\Saved Files\KPI_Grid.xls" _
        , FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False
    ActiveWorkbook.Close


End Sub

Could this be because when I try to open the saved .xls file it tells me that it is not in the specified format?

Regards,

Dave.

 

[Hui]

Dave

Pasting the file and telling us on which line it fails would help us help you

Is there any reason you can't save the file as a *.xlsm file type ?

 

[David Jenkins]

Hi Hui,

Thank you for the response.

I don't think I could share that particular file in the public domain. I've now changed it to the .xlsm format (I thought this one needed to be .xls but it doesn't).

I had commented the above code with '******* errors here *********** to show where the error is originating. Is it because its using Windows and not ActiveWorkbook?

Thank you for looking.

Dave.

 

[Hui]

Dave

On my system it runs fine past that line

What version of Excel and what operating system are you using?
Is the network director definately available ?
Try a local directory like c:\

 

[David Jenkins]

Hi Hui,

Apologies for the delay - pesky weekends!

I am using Excel 2010 and Windows 7 OS.

I have tried a C:\ and the Network Directory was available. They both do the same thing. Its does the first part of the macro fine, it creates the WB and then saves it to the location that I specify. Its then selecting the whole of the sheet ..... and actually just spotted something ....

The new file is saved as .xslm, however, I see that its being opened in compatibility mode and only has 65536 rows .... where the original sheet has the 1048576, so the copy function is trying to paste into a sheet that doesnt have enough rows.... would that cause the issue?

This is the current code that I have in there, do i need to change how its being saved:

Sub Workbook_Open()

    'Create a new workbook
    Dim Wk As Workbook
    Set Wk = Workbooks.Add
    Application.DisplayAlerts = False
    Wk.SaveAs Filename:="C:\Saved File\KPI_Grid.xlsm", FileFormat:=52, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False


    Cells.Select
    Range("B1").Activate
    Selection.Copy
    Windows("KPI_Grid.xlsm").Activate ***still errors here ****
    Cells.Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    ActiveWindow.DisplayGridlines = False
    Sheets("Sheet1").Select
    Sheets("Sheet1").Name = "Weekly"
    Range("B1:E1").Select
    Windows("KPI Grid V5k1 - macro testing.xlsm").Activate
    Sheets("Monthly").Select
    Cells.Select
    Range("B1").Activate
    Application.CutCopyMode = False
    Selection.Copy
    Windows("KPI_Grid.xls").Activate
    Sheets("Sheet2").Select
    Cells.Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    ActiveWindow.DisplayGridlines = False
    Sheets("Sheet2").Select
    Sheets("Sheet2").Name = "Monthly"
    Range("B1:E1").Select
    Application.CutCopyMode = False
    ChDir "C:\Saved File"
    ActiveWorkbook.SaveAs Filename:= _
        "C:\Saved File\KPI_Grid.xlsm" _
        , FileFormat:=52, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False
    ActiveWorkbook.Close


End Sub

Thank you for you're help.

 

[David Jenkins]

:-(. I got this working, its ran successfully once! It then went and corrupted the VBA, just gives me an error with no text! and it wont let me record a macro - its like the VB Screen is completely goosed!

 

[Hui]

It sounds like your Excel/VBA is corrupted
As I said it ran fine on my Excel 2013 Machine
It sounds like you should reinstall Excel and VBA
Unistall them properly first