saving multiple sheets individually ?

excelent

Board Regular
Joined
Sep 7, 2002
Messages
105
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.
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,257
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
 

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,257
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

Board Regular
Joined
Sep 7, 2002
Messages
105

ADVERTISEMENT

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

Board Regular
Joined
Sep 7, 2002
Messages
105
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Try this:

Code:
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

Board Regular
Joined
Sep 7, 2002
Messages
105
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.
 

Forum statistics

Threads
1,144,312
Messages
5,723,653
Members
422,508
Latest member
Lordkit1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top