Why do I keep getting application-defined or object-defined error with simple VBA macro?-CodePudding

I am working on creating a macro to simplify things that I do on a daily basis. The idea behind this macro is to copy 7 worksheets from the parent report, open a new workbook, paste and hardcode the data, save it to my desktop and close the new workbook.

As I am working through debugging and testing my code I am running into a problem which I do not understand why it is happening. If I boot up windows and run the macro the first time in my excel workbook, I get no issues the first time I run it for the 7 reports. The problem comes up when I try to add code or fix issues and run the code again. I keep getting " Run-time error '1004' Application-defined or object defined error".

I am trying to debug and when I hit F8 for debugging and stepping into the code, it seems it gets hung up around step #4 when it is trying to close when I run it more than once. I am learning and using Leila Gharani's course, but I don't think I got this far in the course yet to understand. If someone can please help me so I can understand this concept, I would greatly appreciate it.

Thank you

'1. Select Parent Report and select BD Tab
Workbooks("Parent Report").Activate
Worksheets("BD").Select

'2. Select all cells from BD tab, open new workbook , paste data, and hardcode it.
Cells.Select
Cells.Copy
Workbooks.Add
Range("A1").PasteSpecial xlPasteAll
Cells.Select
Cells.Copy
Range("A1").PasteSpecial xlPasteValues
Cells.EntireRow.AutoFit

'3. Rename worksheet to BD and cell "A2" to BD.
ActiveWorkbook.ActiveSheet.Name = "BD"
Range("A2").Value = "BD"
Range("A1").Select

'4. Save current flash report to local drive, with monthly naming format and close it.
ActiveWorkbook.SaveAs "C:\Users\mylocaldriveinfo\Desktop\Flash Reports" & "\BD Monthly Reporting - Preliminary " & Format(Date, "mmm") & " " & Format(Date, "yyyy")
ActiveWorkbook.Close

CodePudding user response：

I just figured it out. It was my one drive at work giving me an issue. I disabled it, as I never use it and doing so I have not been able to replicate the error.

I am sure this code isn't best practice, but it works for now.

CodePudding user response：

Option Explicit
Sub CopySheets()

    Const FOLDER = "C:\Users\mylocaldriveinfo\Desktop\Flash Reports\BD Monthly Reporting -"

    Dim wbNew As Workbook, wbMaster As Workbook, sht, n As Long

    Set wbMaster = ThisWorkbook

    ' create new workbook
    Set wbNew = Workbooks.Add(1) ' 1 sheet
    n = 1

    For Each sht In Array("BD", "Sheet2", "Sheet3", "Sheet4", _
                      "Sheet5", "Sheet6", "SHeet7") ' 7 sheet names
        
        wbMaster.Sheets(sht).Copy After:=wbNew.Sheets(n)
        n = n   1
        With wbNew.Sheets(n)
            .UsedRange.Value = .UsedRange.Value
            .Range("A2") = sht
        End With
    Next
    
    With wbNew
        ' delete blank sheet
        Application.DisplayAlerts = False
        .Sheets(1).Delete
        Application.DisplayAlerts = True
    
        ' save
        .SaveAs Filename:=FOLDER & Format(Date, "mmm yyyy")
        .Close
    End With
    MsgBox "Done", vbInformation
      
End Sub