saving multiple sheets individually ?

[excelent]

Hi members .

can an excel macro automaticcalyi save sheets individually from a workbook that may contain 100,s of separate sheets.

each sheet would need to be saved in a text format and named the same as the A2 cell in each sheet.
saved in the same folder, e.g. C:sheets


thanks.

mike.

 

[Tom Urtis]

Try this:

Sub SaveSheetz()
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
If sh.Range("A2").Value <> "" Then
sh.Name = sh.Range("A2").Value
End If
Next sh
End Sub

 

[excelent]

Thanks for the formula, but where do the sheets get saved.

mike.

 

[Tom Urtis]

Maybe I'm not clear on your question.

First, what I posted was a macro as you requested, not a formula as you reference in your second post.

Second, the macro saves each sheet tab name as the value of that respective sheet's value in cell A2. If there is no value in A2, the sheet name is not changed (a sheet name cannot be a zero length value).

Maybe you want to save each individual sheet as a workbook name, based on the value in A2 of each sheet? If so, you want to create hundreds of workbooks, as your first post implies? If so again, what if a sheet has no value in A2?

Sorry, I'm not sure what you're asking.

 

[excelent]

sorry,
heres an example.

if my workbook contains 3 sheets,
i would like each sheet saved separately as text.
if cell A2 in sheet 1 contains AA save this sheet as an individual text file named AA.
in a folder e.g. C: testing
so sheet 1 is an individual file now.
if cell A2 in sheet 2 contains BB save this sheet as an individual text file named BB e.t.c..
in a folder e.g. C: testing

This is repeated for all the other sheets.
if i have 100,s of sheets in the 1 workbook there will be 100,s of separate text files in the folder C: testing with different names as there is no repetitive names in the individual workbook sheets

any name down column A would do as long as its not cell A1.

Thanks .
mike.

 

[excelent]

I reread your reply and you probably are right by asking

Maybe you want to save each individual sheet as a workbook name, based on the value in A2 of each sheet? If so, you want to create hundreds of workbooks, as your first post implies? If so again, what if a sheet has no value in A2?

I think this is what im asking but the workbooks need to be saved as as a txt or csv.

if A2 is blank then my best guess is not to save that sheet or stop saving sheets.

Im new to excel and it seems very confusing at the moment.

The VBA manual im using seems more like its aimed at experienced users.

But ill keep at it.

thanks for your help.

mike.

 

[Andrew Poulsom]

Try this:

Sub SaveSheets()
    Const MyPath = "C:Testing"
    Dim Sh As Worksheet
    Dim FName As String
    For Each Sh In ThisWorkbook.Worksheets
        FName = MyPath & Sh.Range("A2").Text
        Sh.Copy
        ActiveWorkbook.SaveAs Filename:=FName, FileFormat:=xlTextPrinter
        ActiveWorkbook.Close Savechanges:=False
    Next Sh
End Sub

 

[excelent]

Thanks andrew,

thats what i was looking for.

works well.

changed xlTextPrinter to xlwindows
to save it as a text file.

what im doing is creating fake tickers after it sorts and places the sorted trade list, (from a share program) decided from the trade dates and from a value generated in a share program of multiple securities in a non random order.

it then converts these lists to a ticker format text file , but each separate tickers trades entry price is now the open value and the trade exit is the close price .
I import these into the share program and run a system backtest test stating the buy as the open, the close as the sell.
It scans the open and close values to give me the compounded result of trading multiple stocks. ( very big improvement of the average trade profit).
Not the average trade of a basket of stocks.

Thanks for the help, this board is great.

Mike.