Try this one

It will save each sheet in C:\ with the name of the sheet

Sub test()
Dim a As Integer
Dim wb As Workbook
Application.ScreenUpdating = False
For a = 1 To ThisWorkbook.Worksheets.Count
ThisWorkbook.Sheets(a).Copy
Set wb = ActiveWorkbook
wb.SaveAs "C:\" & wb.Sheets(1).Name & ".xls"
wb.Close False
Set wb = Nothing
Next a
Application.ScreenUpdating = True
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"lyric2002" wrote in message ...
I am a novice when it comes to macros. I know how to record but am wondering if what I need needs to be written in VB.

I have a worksheet consisting of multiple pages. I have been tasked to copy each page in the worksheet to a seperate workbook.
Does anyone have any code that can do this? Is it possible?

This would work per worksheet, but I need to isolate the pages out of each worksheet with in a workbook. For instance, let's say that each worksheet was per store, and each worksheet had a page for each month with in it. I want to copy to workbooks so page 1 saves to january.xls, page 2 saves to february.xls etc.

"Ron de Bruin" wrote:

Try this one

It will save each sheet in C:\ with the name of the sheet

Sub test()
Dim a As Integer
Dim wb As Workbook
Application.ScreenUpdating = False
For a = 1 To ThisWorkbook.Worksheets.Count
ThisWorkbook.Sheets(a).Copy
Set wb = ActiveWorkbook
wb.SaveAs "C:\" & wb.Sheets(1).Name & ".xls"
wb.Close False
Set wb = Nothing
Next a
Application.ScreenUpdating = True
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"lyric2002" wrote in message ...
I am a novice when it comes to macros. I know how to record but am wondering if what I need needs to be written in VB.

I have a worksheet consisting of multiple pages. I have been tasked to copy each page in the worksheet to a seperate workbook.
Does anyone have any code that can do this? Is it possible?

This would work for a single page worksheet, but I have multiple pages on a worksheet and multiple worksheets in a workbook.

For example I have 12 worksheets in a workbook, one for each month. I have multiple store data (one per page) on a worksheet. I want to save each store's data from the worksheet to a separate workbook so that store can only see it's own data.

"Ron de Bruin" wrote:

Try this one

It will save each sheet in C:\ with the name of the sheet

Sub test()
Dim a As Integer
Dim wb As Workbook
Application.ScreenUpdating = False
For a = 1 To ThisWorkbook.Worksheets.Count
ThisWorkbook.Sheets(a).Copy
Set wb = ActiveWorkbook
wb.SaveAs "C:\" & wb.Sheets(1).Name & ".xls"
wb.Close False
Set wb = Nothing
Next a
Application.ScreenUpdating = True
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"lyric2002" wrote in message ...
I am a novice when it comes to macros. I know how to record but am wondering if what I need needs to be written in VB.

I have a worksheet consisting of multiple pages. I have been tasked to copy each page in the worksheet to a seperate workbook.
Does anyone have any code that can do this? Is it possible?

This would work for a single page worksheet, but I have multiple pages on a worksheet and multiple worksheets in a workbook.

For example I have 12 worksheets in a workbook, one for each month. I have multiple store data (one per page) on a worksheet. I want to save each store's data from the worksheet to a separate workbook so that store can only see it's own data.



"Ron de Bruin" wrote:

Try this one

It will save each sheet in C:\ with the name of the sheet

Sub test()
Dim a As Integer
Dim wb As Workbook
Application.ScreenUpdating = False
For a = 1 To ThisWorkbook.Worksheets.Count
ThisWorkbook.Sheets(a).Copy
Set wb = ActiveWorkbook
wb.SaveAs "C:\" & wb.Sheets(1).Name & ".xls"
wb.Close False
Set wb = Nothing
Next a
Application.ScreenUpdating = True
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"lyric2002" wrote in message ...
I am a novice when it comes to macros. I know how to record but am wondering if what I need needs to be written in VB.

I have a worksheet consisting of multiple pages. I have been tasked to copy each page in the worksheet to a seperate workbook.
Does anyone have any code that can do this? Is it possible?

Have each month in the sheet the same amount of rows

--
Regards Ron de Bruin
http://www.rondebruin.nl


"lyric2002" wrote in message ...
This would work for a single page worksheet, but I have multiple pages on a worksheet and multiple worksheets in a workbook.

For example I have 12 worksheets in a workbook, one for each month. I have multiple store data (one per page) on a worksheet. I
want to save each store's data from the worksheet to a separate workbook so that store can only see it's own data.



"Ron de Bruin" wrote:

Try this one

It will save each sheet in C:\ with the name of the sheet

Sub test()
Dim a As Integer
Dim wb As Workbook
Application.ScreenUpdating = False
For a = 1 To ThisWorkbook.Worksheets.Count
ThisWorkbook.Sheets(a).Copy
Set wb = ActiveWorkbook
wb.SaveAs "C:\" & wb.Sheets(1).Name & ".xls"
wb.Close False
Set wb = Nothing
Next a
Application.ScreenUpdating = True
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"lyric2002" wrote in message ...
I am a novice when it comes to macros. I know how to record but am wondering if what I need needs to be written in VB.

I have a worksheet consisting of multiple pages. I have been tasked to copy each page in the worksheet to a seperate workbook.
Does anyone have any code that can do this? Is it possible?

I received some clarification on my task.

I have a workbook with a worksheet containing 13 pages. There is data for two stores per page. I need to copy the data, paste as values (as there are formulas to suppress) and save as a separate workbook. The goal is to break out the data so each store can only see their own data. The data is contained in the same amount of rows per page.

"Ron de Bruin" wrote:

Have each month in the sheet the same amount of rows

--
Regards Ron de Bruin
http://www.rondebruin.nl


"lyric2002" wrote in message ...
This would work for a single page worksheet, but I have multiple pages on a worksheet and multiple worksheets in a workbook.

For example I have 12 worksheets in a workbook, one for each month. I have multiple store data (one per page) on a worksheet. I
want to save each store's data from the worksheet to a separate workbook so that store can only see it's own data.



"Ron de Bruin" wrote:

Try this one

It will save each sheet in C:\ with the name of the sheet

Sub test()
Dim a As Integer
Dim wb As Workbook
Application.ScreenUpdating = False
For a = 1 To ThisWorkbook.Worksheets.Count
ThisWorkbook.Sheets(a).Copy
Set wb = ActiveWorkbook
wb.SaveAs "C:\" & wb.Sheets(1).Name & ".xls"
wb.Close False
Set wb = Nothing
Next a
Application.ScreenUpdating = True
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"lyric2002" wrote in message ...
I am a novice when it comes to macros. I know how to record but am wondering if what I need needs to be written in VB.

I have a worksheet consisting of multiple pages. I have been tasked to copy each page in the worksheet to a seperate workbook.
Does anyone have any code that can do this? Is it possible?

Ok, try something like this for the Activesheet

It will copy 25 lines to each file.
26 files * 25 lines = 650

It will Save the files in C:\

Sub copy_ActiveSheet_Pages()
Dim wb As Workbook
Dim source As Range
Dim dest As Workbook
Dim a As Long
Dim b As Integer

Application.ScreenUpdating = False
b = 0
For a = 1 To 650 Step 25
b = b + 1
Set source = Rows(a & ":" & a + 24)
Set dest = Workbooks.Add(xlWBATWorksheet)
source.Copy
With dest.Sheets(1)
.Cells(1).PasteSpecial Paste:=8
' Paste:=8 will copy the column width in Excel 2000 and higher
.Cells(1).PasteSpecial xlPasteValues, , False, False
.Cells(1).PasteSpecial xlPasteFormats, , False, False
.Cells(1).Select
Application.CutCopyMode = False
End With

dest.SaveAs Filename:="C:\Store " & b & ".xls"
dest.Close SaveChanges:=False
Next
Application.ScreenUpdating = True
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"lyric2002" wrote in message ...
I received some clarification on my task.

I have a workbook with a worksheet containing 13 pages. There is data for two stores per page. I need to copy the data, paste as
values (as there are formulas to suppress) and save as a separate workbook. The goal is to break out the data so each store can only
see their own data. The data is contained in the same amount of rows per page.

"Ron de Bruin" wrote:

Have each month in the sheet the same amount of rows

--
Regards Ron de Bruin
http://www.rondebruin.nl


"lyric2002" wrote in message ...
This would work for a single page worksheet, but I have multiple pages on a worksheet and multiple worksheets in a workbook.

For example I have 12 worksheets in a workbook, one for each month. I have multiple store data (one per page) on a worksheet.
I
want to save each store's data from the worksheet to a separate workbook so that store can only see it's own data.



"Ron de Bruin" wrote:

Try this one

It will save each sheet in C:\ with the name of the sheet

Sub test()
Dim a As Integer
Dim wb As Workbook
Application.ScreenUpdating = False
For a = 1 To ThisWorkbook.Worksheets.Count
ThisWorkbook.Sheets(a).Copy
Set wb = ActiveWorkbook
wb.SaveAs "C:\" & wb.Sheets(1).Name & ".xls"
wb.Close False
Set wb = Nothing
Next a
Application.ScreenUpdating = True
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"lyric2002" wrote in message
...
I am a novice when it comes to macros. I know how to record but am wondering if what I need needs to be written in VB.

I have a worksheet consisting of multiple pages. I have been tasked to copy each page in the worksheet to a seperate
workbook.
Does anyone have any code that can do this? Is it possible?