Hey Guys,

so I'm trying to have the output data (created earlier in the same Macro) copied to a new Workbook which then would be saved in the same path as the Macro and named using current (system) month. My code is this: 


Dim activePath As String
Dim getMonth As String
Dim newWorkbook As Workbook
Dim newWbkSheet As Worksheet
        
activePath = ActiveWorkbook.Path
getMonth = MonthName(Month(Date))
newWorkbook = Workbooks.add
newWbkSheet = newWorkbook.Worksheets(1)

targetSheet1.Range("A1:AG" & Range("B" & Rows.Count).End(xlUp).Row).copy
newWbkSheet.Range("A1").PasteSpecial xlPasteValues
newWbkSheet.SaveAs Filename:=activePath & "\" & getMonth & ".xlsx"
targetSheet1 is a value defined earlier in the code and means simply the Sheet in which the output (which I want copied to a new Workbook) is located.

When running the macro I get the "Object variable or with block variable not set" error and the newWorkbook = Workbooks.add is highlighted.

The new Workbook gets created, but obviously the macro is interrupted, so nothing is copied and the file is not saved.

I was afraid this would happen as I'm not entirely familiar with working with new workbooks... Can someone explain what happened here and how to correct it?