Copy and paste from one worksheet into a new workbook and save to a particular directory

[bantam999]

Hi

I have cobbled together from several sources code that hopefully copies a range ("AA1:AN1000") from an open worksheet/ workbook and opens a new blank workbook and copies and pastes as formats & values into the new workbook, saves this to a set directory and closes this file.

This is what I have with the line that is causing me issues in red:-

Sub CreateNewWorkbookWithValues()
Dim dirPath, fName As String, newWb As Workbook, thisWb As Workbook
Set thisWb = ThisWorkbook
dirPath = thisWb.Path
fName = ActiveSheet.Range("M2").Value
Application.ScreenUpdating = False
Set newWb = Workbooks.Add
thisWb.Sheets("Summary").Range("AA1:AN1000").Copy newWb.Sheets("Sheet1").Range("A1")
newWb.SaveAs Filename:=NewBook.Worksheets("Sheet1").Range("M1").Value
newWb.Close
Application.ScreenUpdating = True
MsgBox "Workbook created at:" & vbCrLf & vbCrLf & dirPath & "\" & fName
End Sub

It opens a new worksheet/ workbook, copies and pastes but then cannot save the file.

Also, how would I change the code to save it to a particular folder and not just the folder that the original file is saved in

Thanks in advance

 

[Joe4]

Welcome to the Board!

Note the line returning the error, and the part I put in red:

newWb.SaveAs Filename:=NewBook.Worksheets("Sheet1").Range("M1").Value

What exactly is this "NewBook" variable?
I do not see you defining any such variable in your code.
You have "newWb", but not "NewBook".

 

[bantam999]

Welcome to the Board!

Note the line returning the error, and the part I put in red:

newWb.SaveAs Filename:=NewBook.Worksheets("Sheet1").Range("M1").Value

What exactly is this "NewBook" variable?
I do not see you defining any such variable in your code.
You have "newWb", but not "NewBook".

Thanks - I've changed it to this but still does not like the same line - (apologies I am a relative novice with VBA)

Sub CreateNewWorkbookWithValues()
Dim dirPath, fName As String, newWb As Workbook, thisWb As Workbook
Set thisWb = ThisWorkbook
dirPath = thisWb.Path
fName = ActiveSheet.Range("M2").Value
Application.ScreenUpdating = False
Set newWb = Workbooks.Add
thisWb.Sheets("Summary").Range("AA1:AN1000").Copy newWb.Sheets("Sheet1").Range("A1")
newWb.SaveAs Filename:=NewBook.Worksheets("Sheet1").Range("M1").Value
newWb.Close
Application.ScreenUpdating = True
MsgBox "Workbook created at:" & vbCrLf & vbCrLf & dirPath & "\" & fName
End Sub

The network folder I want it to be saved into is :- "H:\***\***\***\***\working on"

Thanks

Bantam

 

[Joe4]

Thanks - I've changed it to this but still does not like the same line - (apologies I am a relative novice with VBA)

You did not update the code you posted - you are still showing/using the undefined "NewBook" reference.

Sub CreateNewWorkbookWithValues()
Dim dirPath, fName As String, newWb As Workbook, thisWb As Workbook
Set thisWb = ThisWorkbook
dirPath = thisWb.Path
fName = ActiveSheet.Range("M2").Value
Application.ScreenUpdating = False
Set newWb = Workbooks.Add
thisWb.Sheets("Summary").Range("AA1:AN1000").Copy newWb.Sheets("Sheet1").Range("A1")
newWb.SaveAs Filename:=NewBook.Worksheets("Sheet1").Range("M1").Value
newWb.Close
Application.ScreenUpdating = True
MsgBox "Workbook created at:" & vbCrLf & vbCrLf & dirPath & "\" & fName
End Sub

Please be sure to use Code Tags when posting your code in the future - it makes it much easier to read.
See: How to Post Your VBA Code