worksheet tabs
 

i have several workbooks with multi tabs in them. how can i generate a
workbook for each tab via vb code?

the number of tabs varies per workbook. is there a way to loop thru total
entries and as each tab is evaluated to generate a workbook whereby tab 1
generates the name stdbook1, tab 2 generates the name stdbook2 and so forth

thanks for your help

worksheet tabs
 

Your description is a bit thin but this should be close to what you want...

Sub stuff()
Dim wks As Worksheet
Dim wbkNew As Workbook
Dim lng As Long

lng = 1
For Each wks In ThisWorkbook.Worksheets
wks.Copy
Set wbkNew = ActiveWorkbook
wbkNew.SaveAs ThisWorkbook.Path & "\stdBook" & lng & ".xls"
wbknew.close
lng = lng + 1
Next wks

End Sub
--
HTH...

Jim Thomlinson


"jnewl" wrote:

i have several workbooks with multi tabs in them. how can i generate a
workbook for each tab via vb code?

the number of tabs varies per workbook. is there a way to loop thru total
entries and as each tab is evaluated to generate a workbook whereby tab 1
generates the name stdbook1, tab 2 generates the name stdbook2 and so forth

thanks for your help

worksheet tabs
 

how do i specify the path?

tried something like this, but did not work
Sub buildbook()
Dim wks As Worksheet
Dim wbknew As Workbook
Dim lng As Long
Dim path As String
lng = 1
For Each wks In ThisWorkbook.Worksheets
wks.Copy
Set wbknew = ActiveWorkbook
path = "H:\hsrstaff\Implementation and MOB\fee sched cons - IT data\SR for
provider listings\topps provider listings from Srs"
wbknew.SaveAs ThisWorkbook.path & "\stdbook" & lng & ".xls"
wbknew.Close
lng = lng + 1
Next wks


thanks

"Jim Thomlinson" wrote:

Your description is a bit thin but this should be close to what you want...

Sub stuff()
Dim wks As Worksheet
Dim wbkNew As Workbook
Dim lng As Long

lng = 1
For Each wks In ThisWorkbook.Worksheets
wks.Copy
Set wbkNew = ActiveWorkbook
wbkNew.SaveAs ThisWorkbook.Path & "\stdBook" & lng & ".xls"
wbknew.close
lng = lng + 1
Next wks

End Sub
--
HTH...

Jim Thomlinson


"jnewl" wrote:

i have several workbooks with multi tabs in them. how can i generate a
workbook for each tab via vb code?

the number of tabs varies per workbook. is there a way to loop thru total
entries and as each tab is evaluated to generate a workbook whereby tab 1
generates the name stdbook1, tab 2 generates the name stdbook2 and so forth

thanks for your help