I have a master file with 25 worksheets. I need to to prerpare four new excel
file from the master sheet.
The first file should contain few sheets from master sheets, second should
contain few, third & so on.

For example:
First file should contain Sheet1, Sheet3, Sheet7, Sheet10
First file should contain Sheet11, Sheet13, Sheet17, Sheet22
and so on.

The names in real file are different, could this be done.
This is something very IMP for me, please help.

Thanks a lot for help in advance.
Boss

These two macros should help
the 1st macro put the sheet names into the active sheet in the workbook.
This is to get the 25 sheet names. Then add into column b (manually) the
workbook names (including path) where you want the worksheets to go.

The second macro reads column A and B of the active worksheet and places the
worksheets into the correct workbooks.


Sub sheetnames()

RowCount = 1
For Each sht In Sheets
Range("A" & RowCount) = sht.Name
RowCount = RowCount + 1
Next sht

End Sub

Sub copysheets()

Set oldbk = ThisWorkbook
Set oldsht = oldbk.ActiveSheet
RowCount = 1
Do While oldsht.Range("A" & RowCount) < ""
Workbooks.Open Filename:=oldsht.Range("B" & RowCount)
Set newbk = ActiveWorkbook
Sheets(oldsht.Range("A" & RowCount)).Copy _
after:=newbk.Sheets(newbk.Sheets.Count)
newbk.Close
RowCount = RowCount + 1
Loop

End Sub


"Boss" wrote:

I have a master file with 25 worksheets. I need to to prerpare four new excel
file from the master sheet.
The first file should contain few sheets from master sheets, second should
contain few, third & so on.

For example:
First file should contain Sheet1, Sheet3, Sheet7, Sheet10
First file should contain Sheet11, Sheet13, Sheet17, Sheet22
and so on.

The names in real file are different, could this be done.
This is something very IMP for me, please help.

Thanks a lot for help in advance.
Boss

Thanks a lot for ur help..

The first macro is working fine but the second is giving me runtime error 13
"Type mismatch"
I kept the entire path of the file in second col. Macro is able to get into
the file also.

Please help & let me know where i am worng.

thanks a lot

Boss

"Joel" wrote:

These two macros should help
the 1st macro put the sheet names into the active sheet in the workbook.
This is to get the 25 sheet names. Then add into column b (manually) the
workbook names (including path) where you want the worksheets to go.

The second macro reads column A and B of the active worksheet and places the
worksheets into the correct workbooks.


Sub sheetnames()

RowCount = 1
For Each sht In Sheets
Range("A" & RowCount) = sht.Name
RowCount = RowCount + 1
Next sht

End Sub

Sub copysheets()

Set oldbk = ThisWorkbook
Set oldsht = oldbk.ActiveSheet
RowCount = 1
Do While oldsht.Range("A" & RowCount) < ""
Workbooks.Open Filename:=oldsht.Range("B" & RowCount)
Set newbk = ActiveWorkbook
Sheets(oldsht.Range("A" & RowCount)).Copy _
after:=newbk.Sheets(newbk.Sheets.Count)
newbk.Close
RowCount = RowCount + 1
Loop

End Sub


"Boss" wrote:

I have a master file with 25 worksheets. I need to to prerpare four new excel
file from the master sheet.
The first file should contain few sheets from master sheets, second should
contain few, third & so on.

For example:
First file should contain Sheet1, Sheet3, Sheet7, Sheet10
First file should contain Sheet11, Sheet13, Sheet17, Sheet22
and so on.

The names in real file are different, could this be done.
This is something very IMP for me, please help.

Thanks a lot for help in advance.
Boss

I made some minor changes

Sub copysheets()

Set oldbk = ThisWorkbook
Set oldsht = oldbk.ActiveSheet
RowCount = 1
Do While oldsht.Range("A" & RowCount) < ""
Workbooks.Open Filename:=oldsht.Range("B" & RowCount)
Set newbk = ActiveWorkbook
oldbk.Sheets(oldsht.Range("A" & RowCount).Value).Copy _
after:=newbk.Sheets(newbk.Sheets.Count)
newbk.Close SaveChanges:=True
RowCount = RowCount + 1
Loop

End Sub


"Boss" wrote:

Thanks a lot for ur help..

The first macro is working fine but the second is giving me runtime error 13
"Type mismatch"
I kept the entire path of the file in second col. Macro is able to get into
the file also.

Please help & let me know where i am worng.

thanks a lot

Boss

"Joel" wrote:

These two macros should help
the 1st macro put the sheet names into the active sheet in the workbook.
This is to get the 25 sheet names. Then add into column b (manually) the
workbook names (including path) where you want the worksheets to go.

The second macro reads column A and B of the active worksheet and places the
worksheets into the correct workbooks.


Sub sheetnames()

RowCount = 1
For Each sht In Sheets
Range("A" & RowCount) = sht.Name
RowCount = RowCount + 1
Next sht

End Sub

Sub copysheets()

Set oldbk = ThisWorkbook
Set oldsht = oldbk.ActiveSheet
RowCount = 1
Do While oldsht.Range("A" & RowCount) < ""
Workbooks.Open Filename:=oldsht.Range("B" & RowCount)
Set newbk = ActiveWorkbook
Sheets(oldsht.Range("A" & RowCount)).Copy _
after:=newbk.Sheets(newbk.Sheets.Count)
newbk.Close
RowCount = RowCount + 1
Loop

End Sub


"Boss" wrote:

I have a master file with 25 worksheets. I need to to prerpare four new excel
file from the master sheet.
The first file should contain few sheets from master sheets, second should
contain few, third & so on.

For example:
First file should contain Sheet1, Sheet3, Sheet7, Sheet10
First file should contain Sheet11, Sheet13, Sheet17, Sheet22
and so on.

The names in real file are different, could this be done.
This is something very IMP for me, please help.

Thanks a lot for help in advance.
Boss

It worked..
Thanks a lot for ur help..
your help made my job easier..

Thanks a lot


"Boss" wrote:

I have a master file with 25 worksheets. I need to to prerpare four new excel
file from the master sheet.
The first file should contain few sheets from master sheets, second should
contain few, third & so on.

For example:
First file should contain Sheet1, Sheet3, Sheet7, Sheet10
First file should contain Sheet11, Sheet13, Sheet17, Sheet22
and so on.

The names in real file are different, could this be done.
This is something very IMP for me, please help.

Thanks a lot for help in advance.
Boss