Still getting a error message of subscript out of range

Question

Every time I run I get error of subscript out of range, data workbook is opened as well.

Sub Macro3()

    Dim FName           As String
    Dim FPath           As String
    Dim NewBook         As Workbook
    Dim IntialName As String
    Dim sFileSaveName As Variant


    Set NewBook = Workbooks.Add
    Workbooks("data").Activate
    ThisWorkbook.Sheets("sheet1").Copy Before:=NewBook.Sheets(1)
    Workbooks("data").Activate
    ThisWorkbook.Sheets("AVI").Copy Before:=NewBook.Sheets(1)

    IntialName = "AVI" & Date
    sFileSaveName = Application.GetSaveAsFilename("AVI " & Format(Date, "dd-mm-yy"), fileFilter:="Excel Files (*.xlsx), *.xlsm")

    If sFileSaveName <> False Then
        ActiveWorkbook.SaveAs sFileSaveName
    End If


End Sub

What line produces the error? – Ron Rosenfeld May 17 '20 at 12:07 — Ron Rosenfeld, May 17 '20 at 12:07

score 0 · Answer 1 · answered May 16 '20 at 18:06

0

Can you try adding the file extension

Workbooks("data.xlsx").Activate

answered May 16 '20 at 18:06

Simon

736
6
21

I put your code in excel and ran it, it does work, so perhaps you can specify what exactly is happening - it must be one of those annoying little things. Make sure the name of the workbook matches and has the correct extension. – Simon May 17 '20 at 10:08

Still getting a error message of subscript out of range

1 Answers1