Export specific range to new workbook

[YasserKhalil]

Hello everyone
I have a sheet names "Basic" and I need to export specific range("A1:AA14") to new workbook ..
The new workbook to be named "Basic" and with the extension "xlsm" and finally close the new created workbook

I need to get rid of formulas and get only the values but keep the formats
I need also to get rid of validation lists (rows 7 : 11)
Also need to remove any named ranges (defined name >> Name manager)

http://www.fileconvoy.com/dfl.php?id=ga32b01e78027595d99979188384b8d53df4a55981

Thanks advanced for help

PS: Why can't I upload directly to the forum?

 

[chirayu]

You'll need to use the SaveAs command as you cant name a workbook without saving it somewhere & if each time the name is "Basic" then you'll have to add in the overwrite command. The rest is relatively easy - copy & value paste with formats will avoid bringing over any named ranges

 

[YasserKhalil]

In fact I have many other sheets .. I need to export this sheet only and not all the sheet just the specific range I mentioned ..
Can you provide me with the code that I can use to get my new workbook?
The workbook will be names" Basic" and if I run the macro again to overwrite the old one without any alert

 

[chirayu]

Sub New_WB()

Dim CurrWB As String
Dim NewWB As String

CurrWB = ActiveWorkbook.Name

Workbooks.Add
NewWB = ActiveWorkbook.Name

Windows(CurrWB).Activate
Worksheets("Basic").Select
Range("A1:AA14").Copy
Windows(NewWB).Activate
Range("A1").Select
ActiveCell.PasteSpecial xlPasteValuesAndNumberFormats
ActiveCell.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
Cells.EntireColumn.AutoFit
Application.GetSaveAsFilename "Basic.xlsm", "Excel Macro-Enabled Workbook (*.xlsm), *.xlsm", , "Save As XLSM file"


End Sub

 

[YasserKhalil]

I tested the code
I run the code and found save as window appeared and when I clicked Save button .. the workbook doesn't be saved .....!
I don't need this saveas window to appear .. I need to save and close the new created workbook
Another point the rows height to be the same and also the columns Widths
Thanks a lot for help

 

[YasserKhalil]

Any help with this topic please

 

[Marc L]

Hi !

At very beginner level !
Just activate Macro Recorder and use SaveAs …
… something like in your own code !
​

 

[YasserKhalil]

Thanks Mr. MarcL for your reply
In fact the code is not mine I just edited to suit the case ..
I am not very expert .. I am just a learner
I explained in detail the desired from the code in first post
Best Regards

 

[Marc L]

To save newworkbook it's like

newWorkbookObject.SaveAs "WorkbookPath\Name.xlsm", XX

where XX is a value that you can find in VBA inner help or
just using Macro Recorder !
​

 

[YasserKhalil]

Thanks Mr. MarcL for reply
The problem is not just the issue of saving the workbook ..
There are many other issues required

I need to get rid of formulas and get only the values but keep the formats
I need also to get rid of validation lists (rows 7 : 11)
Also need to remove any named ranges (defined name >> Name manager)