Macro to save a single worksheet from a multiple sheet workbook into a new workbook

Hi, need help modifying the following macro to create a new workbook from a single sheet in a multiple page workbook.

I need it to do the following:
1- Save just a range (B4:O128) not the entire worksheet
2- Create a folder in the desktop, folder name should be same as value of E16 of that worksheet, say Sheet6
3- Filename the same name as the folder

At the moment line 4 which is commented out generates an error, line 5 does save a copy but puts it on the root directory which is not what I am after, any ideas please!!

Thanks in advance

Please Login or Register  to view this content.

Awesome !!!! when you know you just know

Thank you both

The sheet in question is sheet 6, and it has a particular format that I want to save to the new worksheet.

The code above does save the file and create the folder just fine but only saves the data, no format!!!

We can also save it with the same format right? BTW the format I want to keep is within the range "B4:O128"

Hey guys, im very new and ignorant to VBA. Similar to this, im trying to create a macro button that will save that entire sheet to a separate workbook. The code ive been trying is below, but I have no idea what im doing haha. Any advice as to what im doing wrong. I'm getting a compile:Syntax error.Thanks,

Sub Button3_Click()
Dim wb As Workbook
Set wb = Workbooks.Add
Embed PRG Value Added Sales Tools (Greg).Sheets("Sheet13").Copy Before:=wb.Sheets(1)
wb.SaveAs "C:\temp\test1.xlsx"
End Sub

Good morning Greg94.
Please read the "Forum Rules", at the top. You should be interested in #2.
Have a good weekend.