Subscript out of range Error in a code for selectig a range and adding a value to it

Question

Sub openwb()

Dim sb As String

ChDir "E:\sarath\PTMetrics"

Workbooks.Open ("E:\sarath\PTMetrics\D8 L538-L550_16MY_Powertrain Metrics_" & (Format(Date, "YYYYMMDD") - 1) & ".xlsm")

ActiveWorkbook.SaveAs "E:\sarath\PTMetrics\D8 L538-L550_16MY_Powertrain Metrics_" & (Format(Date, "YYYYMMDD") & ".xlsm")

Set x260wb = ActiveWorkbook

Debug.Print x260wb.Name

Workbooks("x260wb").Sheets("All Concerns").Range(A1).Value = "Hay..."

End Sub

Here on 8th line, i am geting error saying "Subscript out of range". Can u help?..

There are many errors in your code. `1` x260wb is a variable. Remove the " before and after it. `2` A1 is a range. Put that in quotes — Siddharth Rout, Oct 04 '13 at 09:38
This is the 2nd time I am referring you back to [THIS](http://stackoverflow.com/questions/19157385/how-to-open-a-workbook-specifying-its-path) — Siddharth Rout, Oct 04 '13 at 09:39

score 0 · Accepted Answer · edited May 23 '17 at 11:43

Spoon Feeding you for the last time :)

Declare your Objects/Variables. The working becomes much easier if you use them. Avoid the use of ActiveWorkbook/ActiveSheet/Selection.. etc.. INTERESTING READ
There is a difference between variables and strings. Use them appropriately. If you enclose variable names in " then they become string and vice versa i.e if the string is of one word.
You are subtracting 1 from a string Format(Date, "YYYYMMDD") - 1. Format returns a String. See the alternative that I have given in the code below.
While doing a Save As in Excel VBA, you have to specify FileFormat for example, xlsm will have a fileformat of 53 below are the other file formats.

File Formats

50 = xlExcel12 (Excel Binary Workbook in 2007-2013 with or without macro's, xlsb)
51 = xlOpenXMLWorkbook (without macro's in 2007-2013, xlsx)
52 = xlOpenXMLWorkbookMacroEnabled (with or without macro's in 2007-2013, xlsm)
56 = xlExcel8 (97-2003 format in Excel 2007-2013, xls)

Your code (UNTESTED)

Sub openwb()
    Dim wb As Workbook
    Dim sPath As String, sFilename As String, newName As String

    sPath = "E:\sarath\PTMetrics\"
            sFilename = sPath & _
                "D8 L538-L550_16MY_Powertrain Metrics_" & _
                Format(Date - 1, "YYYYMMDD") & _
                ".xlsm"

    Set wb = Workbooks.Open(sFilename)

    newName = sPath & _
              "D8 L538-L550_16MY_Powertrain Metrics_" & _
              Format(Date, "YYYYMMDD") & _
               ".xlsm"

    wb.SaveAs newName, FileFormat:=53

    wb.Sheets("All Concerns").Range("A1").Value = "Hay..."
End Sub

Subscript out of range Error in a code for selectig a range and adding a value to it

1 Answers1