Hi,

I have a routine where I copy a worksheet and place it in order in the
workbook. In testing my workbook, it allowed me to copy 14 sheets then it
stoped working and told me "Copy Method of object worksheet failed" on the
copy after statement. I am unable to determine what is wrong. In debug the
after sheet number is correct.

Closing my workbook and reopening it cleared the problem but I can't have
this happening with the user. If anyone could give some guidance as to how
to trouble shoot this I would greatly appreciate it. Here is my code:

Sub AddSheet(Newrow)
'Copy the worksheet

Dim SheetName As String
Dim ShNumber As Long
Dim AfterShName As String
Dim ShName As String
Dim ShNotLogged As Boolean
Dim TenName As String
Dim msg As String

'check if this is the first tenant sheet
If MainPagepg.Range("BD" & Newrow - 1) = "" Then
ShNumber = Firstpg.Index
Else 'get the name of the sheet before new tenant
AfterShName = MainPagepg.Range("BD" & Newrow - 1).Value
ShNumber = Sheets(AfterShName).Index
End If

TenName = MainPagepg.Range("F" & Newrow).Value

'get the new sheet name
Call NewSheetName(SheetName, TenName)
If SheetName = "" Then Exit Sub

'copy the sheet
Call UnProtectWkbook
CAMMaster.Copy After:=Sheets(ShNumber)

'name the sheet
ActiveSheet.Name = (SheetName)
Call ProtectSht(SheetName)
Call ProtectWkbook

'add links from new sheet to main page
Call AddNameMain(SheetName, Newrow)

'add links & formulas to new sheet from main page
Call AddFormulaLinks(SheetName, Newrow)

'go to top of new page
Sheets(SheetName).Application.Goto Reference:="R4C6"

'unload the form
Unload frmStoreData

'check Master Page for sheets not logged
Call SheetsNotLogged(ShNotLogged)

End Sub


--
Thanks for your help.
Karen53

Check out this article...

http://support.microsoft.com/default...84&Product=xlw
--
HTH...

Jim Thomlinson


"Karen53" wrote:

Hi,

I have a routine where I copy a worksheet and place it in order in the
workbook. In testing my workbook, it allowed me to copy 14 sheets then it
stoped working and told me "Copy Method of object worksheet failed" on the
copy after statement. I am unable to determine what is wrong. In debug the
after sheet number is correct.

Closing my workbook and reopening it cleared the problem but I can't have
this happening with the user. If anyone could give some guidance as to how
to trouble shoot this I would greatly appreciate it. Here is my code:

Sub AddSheet(Newrow)
'Copy the worksheet

Dim SheetName As String
Dim ShNumber As Long
Dim AfterShName As String
Dim ShName As String
Dim ShNotLogged As Boolean
Dim TenName As String
Dim msg As String

'check if this is the first tenant sheet
If MainPagepg.Range("BD" & Newrow - 1) = "" Then
ShNumber = Firstpg.Index
Else 'get the name of the sheet before new tenant
AfterShName = MainPagepg.Range("BD" & Newrow - 1).Value
ShNumber = Sheets(AfterShName).Index
End If

TenName = MainPagepg.Range("F" & Newrow).Value

'get the new sheet name
Call NewSheetName(SheetName, TenName)
If SheetName = "" Then Exit Sub

'copy the sheet
Call UnProtectWkbook
CAMMaster.Copy After:=Sheets(ShNumber)

'name the sheet
ActiveSheet.Name = (SheetName)
Call ProtectSht(SheetName)
Call ProtectWkbook

'add links from new sheet to main page
Call AddNameMain(SheetName, Newrow)

'add links & formulas to new sheet from main page
Call AddFormulaLinks(SheetName, Newrow)

'go to top of new page
Sheets(SheetName).Application.Goto Reference:="R4C6"

'unload the form
Unload frmStoreData

'check Master Page for sheets not logged
Call SheetsNotLogged(ShNotLogged)

End Sub


--
Thanks for your help.
Karen53

Thank you, Jim!
--
Thanks for your help.
Karen53


"Jim Thomlinson" wrote:

Check out this article...

http://support.microsoft.com/default...84&Product=xlw
--
HTH...

Jim Thomlinson


"Karen53" wrote:

Hi,

I have a routine where I copy a worksheet and place it in order in the
workbook. In testing my workbook, it allowed me to copy 14 sheets then it
stoped working and told me "Copy Method of object worksheet failed" on the
copy after statement. I am unable to determine what is wrong. In debug the
after sheet number is correct.

Closing my workbook and reopening it cleared the problem but I can't have
this happening with the user. If anyone could give some guidance as to how
to trouble shoot this I would greatly appreciate it. Here is my code:

Sub AddSheet(Newrow)
'Copy the worksheet

Dim SheetName As String
Dim ShNumber As Long
Dim AfterShName As String
Dim ShName As String
Dim ShNotLogged As Boolean
Dim TenName As String
Dim msg As String

'check if this is the first tenant sheet
If MainPagepg.Range("BD" & Newrow - 1) = "" Then
ShNumber = Firstpg.Index
Else 'get the name of the sheet before new tenant
AfterShName = MainPagepg.Range("BD" & Newrow - 1).Value
ShNumber = Sheets(AfterShName).Index
End If

TenName = MainPagepg.Range("F" & Newrow).Value

'get the new sheet name
Call NewSheetName(SheetName, TenName)
If SheetName = "" Then Exit Sub

'copy the sheet
Call UnProtectWkbook
CAMMaster.Copy After:=Sheets(ShNumber)

'name the sheet
ActiveSheet.Name = (SheetName)
Call ProtectSht(SheetName)
Call ProtectWkbook

'add links from new sheet to main page
Call AddNameMain(SheetName, Newrow)

'add links & formulas to new sheet from main page
Call AddFormulaLinks(SheetName, Newrow)

'go to top of new page
Sheets(SheetName).Application.Goto Reference:="R4C6"

'unload the form
Unload frmStoreData

'check Master Page for sheets not logged
Call SheetsNotLogged(ShNotLogged)

End Sub


--
Thanks for your help.
Karen53

Hi,

I've never done this before. My path is Desktop\Karen\CAMMasterTemp.

When I use the statement:
Sheets.Add Type:="Desktop \ Karen \ Version11 \ CAMMasterTemp"

I get a 1004 error. The folder is not read only. What confuses me is it
gives me back the name of my template file as characters I cannot use, i.e. {
} : CAMMasterTemp *........etc.

What am I doing wrong?
--
Thanks for your help.
Karen53


"Jim Thomlinson" wrote:

Check out this article...

http://support.microsoft.com/default...84&Product=xlw
--
HTH...

Jim Thomlinson


"Karen53" wrote:

Hi,

I have a routine where I copy a worksheet and place it in order in the
workbook. In testing my workbook, it allowed me to copy 14 sheets then it
stoped working and told me "Copy Method of object worksheet failed" on the
copy after statement. I am unable to determine what is wrong. In debug the
after sheet number is correct.

Closing my workbook and reopening it cleared the problem but I can't have
this happening with the user. If anyone could give some guidance as to how
to trouble shoot this I would greatly appreciate it. Here is my code:

Sub AddSheet(Newrow)
'Copy the worksheet

Dim SheetName As String
Dim ShNumber As Long
Dim AfterShName As String
Dim ShName As String
Dim ShNotLogged As Boolean
Dim TenName As String
Dim msg As String

'check if this is the first tenant sheet
If MainPagepg.Range("BD" & Newrow - 1) = "" Then
ShNumber = Firstpg.Index
Else 'get the name of the sheet before new tenant
AfterShName = MainPagepg.Range("BD" & Newrow - 1).Value
ShNumber = Sheets(AfterShName).Index
End If

TenName = MainPagepg.Range("F" & Newrow).Value

'get the new sheet name
Call NewSheetName(SheetName, TenName)
If SheetName = "" Then Exit Sub

'copy the sheet
Call UnProtectWkbook
CAMMaster.Copy After:=Sheets(ShNumber)

'name the sheet
ActiveSheet.Name = (SheetName)
Call ProtectSht(SheetName)
Call ProtectWkbook

'add links from new sheet to main page
Call AddNameMain(SheetName, Newrow)

'add links & formulas to new sheet from main page
Call AddFormulaLinks(SheetName, Newrow)

'go to top of new page
Sheets(SheetName).Application.Goto Reference:="R4C6"

'unload the form
Unload frmStoreData

'check Master Page for sheets not logged
Call SheetsNotLogged(ShNotLogged)

End Sub


--
Thanks for your help.
Karen53

Sorry, Version11 is part of the path.
--
Thanks for your help.
Karen53


"Karen53" wrote:

Hi,

I've never done this before. My path is Desktop\Karen\CAMMasterTemp.

When I use the statement:
Sheets.Add Type:="Desktop \ Karen \ Version11 \ CAMMasterTemp"

I get a 1004 error. The folder is not read only. What confuses me is it
gives me back the name of my template file as characters I cannot use, i.e. {
} : CAMMasterTemp *........etc.

What am I doing wrong?
--
Thanks for your help.
Karen53


"Jim Thomlinson" wrote:

Check out this article...

http://support.microsoft.com/default...84&Product=xlw
--
HTH...

Jim Thomlinson


"Karen53" wrote:

Hi,

I have a routine where I copy a worksheet and place it in order in the
workbook. In testing my workbook, it allowed me to copy 14 sheets then it
stoped working and told me "Copy Method of object worksheet failed" on the
copy after statement. I am unable to determine what is wrong. In debug the
after sheet number is correct.

Closing my workbook and reopening it cleared the problem but I can't have
this happening with the user. If anyone could give some guidance as to how
to trouble shoot this I would greatly appreciate it. Here is my code:

Sub AddSheet(Newrow)
'Copy the worksheet

Dim SheetName As String
Dim ShNumber As Long
Dim AfterShName As String
Dim ShName As String
Dim ShNotLogged As Boolean
Dim TenName As String
Dim msg As String

'check if this is the first tenant sheet
If MainPagepg.Range("BD" & Newrow - 1) = "" Then
ShNumber = Firstpg.Index
Else 'get the name of the sheet before new tenant
AfterShName = MainPagepg.Range("BD" & Newrow - 1).Value
ShNumber = Sheets(AfterShName).Index
End If

TenName = MainPagepg.Range("F" & Newrow).Value

'get the new sheet name
Call NewSheetName(SheetName, TenName)
If SheetName = "" Then Exit Sub

'copy the sheet
Call UnProtectWkbook
CAMMaster.Copy After:=Sheets(ShNumber)

'name the sheet
ActiveSheet.Name = (SheetName)
Call ProtectSht(SheetName)
Call ProtectWkbook

'add links from new sheet to main page
Call AddNameMain(SheetName, Newrow)

'add links & formulas to new sheet from main page
Call AddFormulaLinks(SheetName, Newrow)

'go to top of new page
Sheets(SheetName).Application.Goto Reference:="R4C6"

'unload the form
Unload frmStoreData

'check Master Page for sheets not logged
Call SheetsNotLogged(ShNotLogged)

End Sub


--
Thanks for your help.
Karen53