CODEWITHSUNDEEP
excelvba
Captionless
Captionless

Reputation: 63

Save as file with dynamic date from the same location

I am encountering an error on the code that I am working on below.

Sub Pasting
    Dim o as integer
    Dim i as integer
    Dim v as String

    o = 1
    i = 0

    Sheets("Sample").Visible = True
    Sheets("Sample").Select

    Do While i < 1
        Range("A:AA").Select
        Selection.Copy

        ActiveSheet.Next.Select
        On Error Goto PE

        Range("A1").Select
        Application.DisplayAlerts = False
        ActiveSheet.Paste
        Application.DisplayAlerts = True
    Loop

PE:
    Application.CutCopyMode = False
    Sheets("Sample").Visible = False
    Sheeets("Overall").Select

    v = "Sample File" & Format(DateAdd("m",1,Now), "Mmmm yyyy") & ".xlsb"
    ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & v
End Sub

My Error was a

Run-Time Error 1004
Method 'SaveAs' of object'_Workbook Failed.

Process would be:

  1. You will open the Previous File from the previous month

  2. Click file to Open Sample sheet w/c contains default table

  3. Run the loop until all all sheets from 1 to 30 has been paste with default data

  4. Macro will end loop

  5. Macro will save the file as same file type from the same location with the new month.

  6. Close file and override error messages.

Upvotes: 3

Views: 315

Answers (1)

QHarr
QHarr

Reputation: 84465

At least three things

  1. You should use a workbook name/variable rather than ActiveWorkbook to avoid accidentally using the wrong workbook. I can't see from your code where ActiveWorkbook comes from. One danger could be that it is unintentionally ThisWorkbook.
  2. If ThisWorkbook isn't already saved then the ThisWorkbook.Path will be "" and you will get an error
  3. You should specify the file format for xlsb on save (though if already an xlsb there won't be an error)

VBA:

Dim v As String
v = "Sample File" & Format(DateAdd("m", 1, Now), "Mmmm yyyy") & ".xlsb"
ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & v, FileFormat:=50

Upvotes: 1

Related Questions