Unable to create new Workbook and copy data into it

Try this code with the changes made.

Dim activePath As String
Dim getMonth As String
Dim newWorkbook As New Workbook ' changed
Dim newWbkSheet As Worksheet
        
activePath = ActiveWorkbook.Path
getMonth = MonthName(Month(Date))
Set newWorkbook = Workbooks.Add 'changed
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"

Hey,

thanks for the suggestions. It does not show an error now and creates the new file, and saves it, and names it with the month, BUT...

It only copies Range A1:AG1...

Shouldn't this line:

targetSheet1.Range("A1:AG" & Range("B" & Rows.Count).End(xlUp).Row).Copy

Determine which row in column B is the last (data input to the macro varies, so it will never be exactly same number of rows) and apply the row number to the column AG? At least that was my intention, but now it only seems to copy A1 until AG1...


EDIT

I've figured it out. I think the variable "targetSheet1" wasn't used properly anymore (earlier in the code it's simply being defined as ActiveWorkbook, but since a new Workbook is created, it probably gets confusing as to which Workbook should be treated as Active... Just my guess though.).

In case anyone needed it, below I paste the code with my adjustments:

Application.ScreenUpdating = False
    Dim activePath As String
    Dim getMonth As String
    Dim newWorkbook As New Workbook
    Dim newWbkSheet As Worksheet
    Dim finalSheet As Worksheet
    
    lastRow = ThisWorkbook.Sheets("INPUT-OUTPUT").Cells(Rows.Count, 2).End(xlUp).Row
    Set finalSheet = ThisWorkbook.Sheets("INPUT-OUTPUT")
    activePath = ActiveWorkbook.Path
    getMonth = MonthName(Month(Date))
    Set newWorkbook = Workbooks.add
    Set newWbkSheet = newWorkbook.Worksheets(1)

' copy results from INPUT-OUTPUT to a new Workbook
    finalSheet.Range("A1:AG" & lastRow).copy
    newWbkSheet.Range("A1").PasteSpecial xlValues
    newWbkSheet.SaveAs Filename:=activePath & "\" & getMonth & ".xlsx"
    ActiveWorkbook.Close False
    Application.ScreenUpdating = True

So thanks xlbiznes for the "New workbook" and "Set" prefix clarification!

That code will actually find the last cell in column B in the active sheet of the new workbook, to get the last row in column B in targetSheet1 you need to add a worksheet reference.

targetSheet1.Range("A1:AG" & targetSheet1.Range("B" & Rows.Count).End(xlUp).Row).Copy