I'm not sure why it stopped after one sheet. Your code worked for me.

Option Explicit
Sub Createbk()
Dim wks As Worksheet
Dim wbkNew As Workbook
Dim lng As Long
Dim myPath As String

myPath = "C:\my documents\excel\test" '<-- change this
If Right(myPath, 1) < "\" Then
myPath = myPath & "\"
End If

lng = 1
For Each wks In ThisWorkbook.Worksheets
wks.Copy
Set wbkNew = ActiveWorkbook
wbkNew.SaveAs myPath & "stdBook" & lng & ".xls"
wbkNew.Close savechanges:=False
lng = lng + 1
Next wks

End Sub

But watch out for typos he

mypath = "G.\master folder\build access table"

That dot should be a colon and that folder has to exist on the drive.

jnewl wrote:

i have several workbooks that have multi tabs. i need to generate a workbook
for each tab, so that i can import the books to access to build a master
table. the tabs are different in each excel workbook. therefore, i would like
to generate standard names when extracting each tab. for example, tab 1 would
be stdbook1, tab 2 would be stdbook2. that way i can build an append query
in access that will take each standard name and load to a master table,
regardless of original name in excel.

i got this code from an earlier request:

Sub createbk()
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

the problem is only takes the first tab and stops.

plus i can not control the path

so i tried something like this:

placed a dim statement above - dim path as long

then in the wks.copy section had
path = "G.\master folder\build access table"

then kept the rest of the code.

that did not work.

i tried building vb code in access to take each tab from the workbook and
then import but that did not work, so i went the above route.

so. how do i get it so it will go past the first tab and how do i build the
path for the output location

thanks much

--

Dave Peterson