Results 1 to 6 of 6

Thread: VBA Save As not working.....
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Nov 2013
    Location
    Glasgow
    Posts
    135
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA Save As not working.....

    Hi,

    I am trying to save a new workbook, but it will not work. Get a runtime 1004 error.

    Can anyone help?

    Code:
    Sub test()
    
    Dim wb As Workbook
    Dim wb2 As Workbook
    Dim ws As Worksheet
    Dim dte As String
    Set wb = ThisWorkbook
    Set ws = wb.Sheets("Signed EUR")
    dte = ws.Range("N28").Value
    ws.Cells.Copy
    Set wb2 = Workbooks.Add
    wb2.Sheets(1).Cells(1, 1).PasteSpecial xlPasteValues
    wb2.SaveAs Filename:="S:\QA\Clients\Insight\" & dte & ".xlsx", FileFormat:=xlNormal
     
    End Sub

  2. #2
    New Member
    Join Date
    Apr 2015
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Save As not working.....

    What do you have under the "dte" variable?

  3. #3
    Board Regular
    Join Date
    Nov 2013
    Location
    Glasgow
    Posts
    135
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Save As not working.....

    It's a date

  4. #4

    Join Date
    Jun 2013
    Location
    Scotland
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Save As not working.....

    While I am no pro, I think the issue may be with the format you are saving as. You initially instruct to save in .xlsx, however later instruct to save as xlnormal (which I believe to be xls). I would suggest removing the xlNormal.

    old
    "wb2.SaveAs Filename:="S:\QA\Clients\Insight\" & dte & ".xlsx", FileFormat:=xlNormal"

    new
    wb2.SaveAs Filename:="S:\QA\Clients\Insight\" & dte & ".xlsx"

    Regards
    Gary Waddell

  5. #5
    Board Regular
    Join Date
    Nov 2013
    Location
    Glasgow
    Posts
    135
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Save As not working.....

    I think it has something to do with the slashes in the date.

    How do I get rid of them?

  6. #6
    Board Regular
    Join Date
    Nov 2013
    Location
    Glasgow
    Posts
    135
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Save As not working.....

    Scratch that.

    I have got rid of the slashes, but now getting the error message: "the specified dimension is not valid for the current chart type"

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •