Solved: name of folder

[reza_doang]

hi all,

i got vba from here about save sheets as book.
from that vba, if i run macro all files will save in a folder which name of folder depend on name of file.
now i need someone who expertize in vba modify little macro for me.
since all worksheets that i have, had same structure/headers, so i want if i run macro, it will create folder and name it based on cell F7.


Option Explicit

Sub SaveShtsAsBook()
Dim Sheet As Worksheet, SheetName$, MyFilePath$, N&
MyFilePath$ = ActiveWorkbook.Path & "\" & _
Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4)
With Application
.ScreenUpdating = False
.DisplayAlerts = False
' End With
On Error Resume Next '<< a folder exists
MkDir MyFilePath '<< create a folder
For N = 1 To Sheets.Count

Sheets(N).Activate
SheetName = ActiveSheet.Name
Cells.Copy
Workbooks.Add (xlWBATWorksheet)
With ActiveWorkbook
With .ActiveSheet
.Paste
.Name = SheetName
[A1].Select
End With
'save book in this folder
.SaveAs FileName:=MyFilePath _
& "\" & SheetName & ".xlsx"
.Close SaveChanges:=True
End With
.CutCopyMode = False
Next
End With
Sheet1.Activate
End Sub


many thanks for your help

reza

[Simon Lloyd]

Change this bit .SaveAs FileName:=MyFilePath _
& "\" & SheetName & ".xlsxfor this
.SaveAs FileName:=MyFilePath _
& "\" & Sheets(SheetName).Range("F7") & ".xlsx

[reza_doang]

not working...still save with file's name.

[Simon Lloyd]

not working...still save with file's name.Really? post your code as it is now or your workbook!

[Simon Lloyd]

The xls will save exactly as you wanted (as the contents of F7) if you want the folder the same name then replace thisMyFilePath$ = ActiveWorkbook.Path & "\" & _
Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4)withMyFilePath$ = ActiveWorkbook.Path & "\" & _
ActiveSheet.Range("F7").Value

[reza_doang]

simon...

this my sample...actually i have more rows and files.
i drag all data in Count of ID and run Pivot Macro, after that it will create a new worksheets. then i run SaveShstAsBook...then it will save all worksheet into a folder with name folder same with name of file which is "sample".
now when i run this macro, it will create a new folder with name like in cell "E2".
many thanks before and after.

reza

[Simon Lloyd]

I've just taken a look at your workbook, i won't be helping any further!

You haven't implemented ANY of my suggestions, if your not willing to try, use the suggestion and try to work some of it yourself im not willing to help, you're wasting my time!

[reza_doang]

simon...

yes, already try your both suggestion before, but it didn't work. that's why i attach my original workbook.
your first suggestion, i tried but not work, still save with name of file.
second suggestion, i tried, but even not automatically save into a folder. i should write name of file first.
after that, i attach my original my file.

but if you don't want help anymore, its okay.
thanks anyway for your help before.

best regards,

reza

[Simon Lloyd]

First you have nothing in F4 of the pivot sheet, secondly having both my changes in place works perfectly i've just tried it!

[slamet Harto]

Reza

another one. I would suggest to resizing the file before posting in this forum.

Sorry if this is not for helping you anything.
Regards,
Harto

[Simon Lloyd]

Reza

another one. I would suggest to resizing the file before posting in this forum.

Sorry if this is not for helping you anything.
Regards,
HartoWhy? the file size is small!!!! :doh:

[nepotist]

simon...

yes, already try your both suggestion before, but it didn't work. that's why i attach my original workbook.
your first suggestion, i tried but not work, still save with name of file.
second suggestion, i tried, but even not automatically save into a folder. i should write name of file first.
after that, i attach my original my file.

but if you don't want help anymore, its okay.
thanks anyway for your help before.

best regards,

reza

As I understand you are expecting this macro to do three things:

1) Create a folder with the same name as the current workbook. In this case (based on the sample that you have attached) the name of the folder should be "sample"

2) it should save all the worksheets in the sample workbook individually as workbooks in the folder created above. In this case it should save a pivot worksheet as pivot workbook in the sample folder and the same for the other worksheets.

3) it should create another folder with the name same as the value in the cell "E2".

The current macro does the first two thing. Where do you want to create the second folder with the name in cell"E2"?
Do you want to create in the sample folder or in the folder where the sample folder is located or some place else.

[reza_doang]

nepotist...

no, i don't want to create another folder, i just want the name of folder should same with cell "E2".

if i use current macro that i got in this forum, all worksheets will save in a folder with name of folder same with name of file. (i.e, if my excel name's reza then i will create folder reza and all worksheets will save there)

now when i run that macro, i want the name of folder will follow from cell "E2" (cause E1 as header).
i.e, i have 1 excel file with name's "nepotis", in that file there are 10 worksheets (sheet1 -10), and all worksheet have same structure/headers and for column E in ALL worksheets have same values, let say "reza".
and when i run macro, i want all worksheets in that file will save as workbooks and save it into a folder with name "reza" which take from "E2".

that what i want to achieve...

many thanks