Copy a Sheet to New Sheet Q

[Sean]

How would I copy the active Sheet to a newly created Sheet, then
rename "New Sheet" to the value in A5 in the Active sheet, then return
the cursor to the active sheet?

I have essentially a workbook that builds up a new sheet each week
(name of new sheet would be in format dd/mm/yy)


Thanks

 

[Zone]

Copy the code below and paste it in a standard module. HTH, James

Sub CopySht()
Dim shtName As String
shtName = ActiveSheet.Name
ActiveSheet.Copy after:=Sheets(Sheets.Count)
ActiveSheet.Name = [a5]
Sheets(shtName).Activate
End Sub

 

[Sean]

Copy the code below and paste it in a standard module. HTH, James

Sub CopySht()
Dim shtName As String
shtName = ActiveSheet.Name
ActiveSheet.Copy after:=Sheets(Sheets.Count)
ActiveSheet.Name = [a5]
Sheets(shtName).Activate
End Sub

How would I copy the active Sheet to a newly created Sheet, then
rename "New Sheet" to the value in A5 in the Active sheet, then return
the cursor to the active sheet?

I have essentially a workbook that builds up a new sheet each week
(name of new sheet would be in format dd/mm/yy)

Thanks- Hide quoted text -

- Show quoted text -

Thanks James, but I'm hitting debug. Also my value in A5 is in the
format DD/MM/YY, but your code creates a new sheet with the same name
as the 'Original' except an appendix eg Master(1), but I wish to have
the sheet created as 19-08-07, if A5=19/08/07 etc etc

Thanks

 

[Zone]

Sean, I didn't realize A5 had a date in it. Try changing the
ActiveSheet.Name line to this:

ActiveSheet.Name = Format([a5], "dd-mm-yy")

That should fix the problem. James

Copy the code below and paste it in a standard module. HTH, James

Sub CopySht()
Dim shtName As String
shtName = ActiveSheet.Name
ActiveSheet.Copy after:=Sheets(Sheets.Count)
ActiveSheet.Name = [a5]
Sheets(shtName).Activate
End Sub

How would I copy the active Sheet to a newly created Sheet, then
rename "New Sheet" to the value in A5 in the Active sheet, then return
the cursor to the active sheet?

I have essentially a workbook that builds up a new sheet each week
(name of new sheet would be in format dd/mm/yy)

Thanks- Hide quoted text -

- Show quoted text -

Thanks James, but I'm hitting debug. Also my value in A5 is in the
format DD/MM/YY, but your code creates a new sheet with the same name
as the 'Original' except an appendix eg Master(1), but I wish to have
the sheet created as 19-08-07, if A5=19/08/07 etc etc

Thanks

 

[Sean]

Sean, I didn't realize A5 had a date in it. Try changing the
ActiveSheet.Name line to this:

ActiveSheet.Name = Format([a5], "dd-mm-yy")

That should fix the problem. James

Copy the code below and paste it in a standard module. HTH, James
Sub CopySht()
Dim shtName As String
shtName = ActiveSheet.Name
ActiveSheet.Copy after:=Sheets(Sheets.Count)
ActiveSheet.Name = [a5]
Sheets(shtName).Activate
End Sub

How would I copy the active Sheet to a newly created Sheet, then
rename "New Sheet" to the value in A5 in the Active sheet, then return
the cursor to the active sheet?
I have essentially a workbook that builds up a new sheet each week
(name of new sheet would be in format dd/mm/yy)
Thanks- Hide quoted text -
- Show quoted text -

Thanks James, but I'm hitting debug. Also my value in A5 is in the
format DD/MM/YY, but your code creates a new sheet with the same name
as the 'Original' except an appendix eg Master(1), but I wish to have
the sheet created as 19-08-07, if A5=19/08/07 etc etc

Thanks- Hide quoted text -

- Show quoted text -

Spot on James. One final tweak. It places the new sheet at the end of
my sheet list, how would I place it just to the right of my original
sheet?

Thanks

 

[Zone]

Change the ActiveSheet.Copy line like this:

ActiveSheet.Copy after:=ActiveSheet

 

[Sean]

Change the ActiveSheet.Copy line like this:

ActiveSheet.Copy after:=ActiveSheet







- Show quoted text -

Thanks for your help James

 

[Sean]

Thanks for your help James- Hide quoted text -

- Show quoted text -

James can I test your knowledge again? If an attempt was made to
create a sheet with the same name / date (which I can't have). How in
this instance if it happened could I cancel out of the macro with a
message box saying "You have already created this week"?

Thanks

 

[Zone]

Sean, change it like this and add the new function as shown. James

Sub CopySht()
Dim shtName As String, newShtName As String
shtName = ActiveSheet.Name
newShtName = Format([a5], "dd-mm-yy")
If Not SheetExists(newShtName) Then
ActiveSheet.Copy after:=ActiveSheet
ActiveSheet.Name = newShtName
Sheets(shtName).Activate
Else
MsgBox "You have already created this week.", vbCritical
End If
End Sub

Private Function SheetExists(sname) As Boolean
'from John Walkenbach
Dim x As Object
On Error Resume Next
Set x = ActiveWorkbook.Sheets(sname)
If Err = 0 Then SheetExists = True _
Else SheetExists = False
End Function

 

[Sean]

Sean, change it like this and add the new function as shown. James

Sub CopySht()
Dim shtName As String, newShtName As String
shtName = ActiveSheet.Name
newShtName = Format([a5], "dd-mm-yy")
If Not SheetExists(newShtName) Then
ActiveSheet.Copy after:=ActiveSheet
ActiveSheet.Name = newShtName
Sheets(shtName).Activate
Else
MsgBox "You have already created this week.", vbCritical
End If
End Sub

Private Function SheetExists(sname) As Boolean
'from John Walkenbach
Dim x As Object
On Error Resume Next
Set x = ActiveWorkbook.Sheets(sname)
If Err = 0 Then SheetExists = True _
Else SheetExists = False
End Function

James can I test your knowledge again? If an attempt was made to
create a sheet with the same name / date (which I can't have). How in
this instance if it happened could I cancel out of the macro with a
message box saying "You have already created this week"?

Thanks- Hide quoted text -

- Show quoted text -

James, I'm getting a debug on line "If Not SheetExists(newShtName)
Then", saying sub or function not defined

 

[Zone]

Did you copy the function from my post and paste it in the same module as
the subroutine?

Sean, change it like this and add the new function as shown. James

Sub CopySht()
Dim shtName As String, newShtName As String
shtName = ActiveSheet.Name
newShtName = Format([a5], "dd-mm-yy")
If Not SheetExists(newShtName) Then
ActiveSheet.Copy after:=ActiveSheet
ActiveSheet.Name = newShtName
Sheets(shtName).Activate
Else
MsgBox "You have already created this week.", vbCritical
End If
End Sub

Private Function SheetExists(sname) As Boolean
'from John Walkenbach
Dim x As Object
On Error Resume Next
Set x = ActiveWorkbook.Sheets(sname)
If Err = 0 Then SheetExists = True _
Else SheetExists = False
End Function

James can I test your knowledge again? If an attempt was made to
create a sheet with the same name / date (which I can't have). How in
this instance if it happened could I cancel out of the macro with a
message box saying "You have already created this week"?

Thanks- Hide quoted text -

- Show quoted text -

James, I'm getting a debug on line "If Not SheetExists(newShtName)
Then", saying sub or function not defined

 

[Sean]

Did you copy the function from my post and paste it in the same module as
the subroutine?

Sean, change it like this and add the new function as shown. James
Sub CopySht()
Dim shtName As String, newShtName As String
shtName = ActiveSheet.Name
newShtName = Format([a5], "dd-mm-yy")
If Not SheetExists(newShtName) Then
ActiveSheet.Copy after:=ActiveSheet
ActiveSheet.Name = newShtName
Sheets(shtName).Activate
Else
MsgBox "You have already created this week.", vbCritical
End If
End Sub
Private Function SheetExists(sname) As Boolean
'from John Walkenbach
Dim x As Object
On Error Resume Next
Set x = ActiveWorkbook.Sheets(sname)
If Err = 0 Then SheetExists = True _
Else SheetExists = False
End Function
James can I test your knowledge again? If an attempt was made to
create a sheet with the same name / date (which I can't have). How in
this instance if it happened could I cancel out of the macro with a
message box saying "You have already created this week"?
Thanks- Hide quoted text -
- Show quoted text -

James, I'm getting a debug on line "If Not SheetExists(newShtName)
Then", saying sub or function not defined- Hide quoted text -

- Show quoted text -

Bingo, thought I had to have Private Sub's in This Workbook.

One very last question, as my original sheet has a Red tab colour and
I don't wish to have any colour on the new how would I incorporate
something like this in (that works) newShtName.Tab.ColorIndex =
-4142

 

[Sean]

Did you copy the function from my post and paste it in the same module as
the subroutine?

Sean, change it like this and add the new function as shown. James
Sub CopySht()
Dim shtName As String, newShtName As String
shtName = ActiveSheet.Name
newShtName = Format([a5], "dd-mm-yy")
If Not SheetExists(newShtName) Then
ActiveSheet.Copy after:=ActiveSheet
ActiveSheet.Name = newShtName
Sheets(shtName).Activate
Else
MsgBox "You have already created this week.", vbCritical
End If
End Sub
Private Function SheetExists(sname) As Boolean
'from John Walkenbach
Dim x As Object
On Error Resume Next
Set x = ActiveWorkbook.Sheets(sname)
If Err = 0 Then SheetExists = True _
Else SheetExists = False
End Function
James can I test your knowledge again? If an attempt was made to
create a sheet with the same name / date (which I can't have). How in
this instance if it happened could I cancel out of the macro with a
message box saying "You have already created this week"?
Thanks- Hide quoted text -
- Show quoted text -

James, I'm getting a debug on line "If Not SheetExists(newShtName)
Then", saying sub or function not defined- Hide quoted text -

- Show quoted text -

shtName = ActiveSheet.Name
newShtName = Format([a5], "dd-mm-yy")
If Not SheetExists(newShtName) Then
ActiveSheet.Copy after:=ActiveSheet
ActiveSheet.Name = newShtName
ActiveSheet.Tab.ColorIndex = -4142
Sheets(shtName).Activate

Just done it! Thanks for your help James

 

[Zone]

You're welcome, Sean. Nice working with you. Cheers, James

Did you copy the function from my post and paste it in the same module as
the subroutine?

Sean, change it like this and add the new function as shown. James

Sub CopySht()
Dim shtName As String, newShtName As String
shtName = ActiveSheet.Name
newShtName = Format([a5], "dd-mm-yy")
If Not SheetExists(newShtName) Then
ActiveSheet.Copy after:=ActiveSheet
ActiveSheet.Name = newShtName
Sheets(shtName).Activate
Else
MsgBox "You have already created this week.", vbCritical
End If
End Sub

Private Function SheetExists(sname) As Boolean
'from John Walkenbach
Dim x As Object
On Error Resume Next
Set x = ActiveWorkbook.Sheets(sname)
If Err = 0 Then SheetExists = True _
Else SheetExists = False
End Function

James can I test your knowledge again? If an attempt was made to
create a sheet with the same name / date (which I can't have). How
in
this instance if it happened could I cancel out of the macro with a
message box saying "You have already created this week"?

Thanks- Hide quoted text -

- Show quoted text -

James, I'm getting a debug on line "If Not SheetExists(newShtName)
Then", saying sub or function not defined- Hide quoted text -

- Show quoted text -

shtName = ActiveSheet.Name
newShtName = Format([a5], "dd-mm-yy")
If Not SheetExists(newShtName) Then
ActiveSheet.Copy after:=ActiveSheet
ActiveSheet.Name = newShtName
ActiveSheet.Tab.ColorIndex = -4142
Sheets(shtName).Activate

Just done it! Thanks for your help James

 

[JockW]

James,
Can this code be tweaked to:
Take the date from A5 and use as new sheet tab name with 7 days added to it
and then paste this new date in A5 in the new sheet?

Thanks,

 

[Zone]

Sub CopyShtPlus7Days()
Dim shtName As String, newShtName As String
shtName = ActiveSheet.Name
newShtName = Format([a5] + 7, "dd-mm-yy") 'line changed
If Not SheetExists(newShtName) Then
ActiveSheet.Copy after:=ActiveSheet
ActiveSheet.Name = newShtName
[a5] = newShtName 'new line
Sheets(shtName).Activate
Else
MsgBox "You have already created this week.", vbCritical
End If
End Sub

 

[Zone]

Jock, the code I posted has a mistake that will show up on subsequent runs.
Change line
[a5]=newShtName 'new line
like this:
[a5] = CDate(newShtName) 'new line
James

Sub CopyShtPlus7Days()
Dim shtName As String, newShtName As String
shtName = ActiveSheet.Name
newShtName = Format([a5] + 7, "dd-mm-yy") 'line changed
If Not SheetExists(newShtName) Then
ActiveSheet.Copy after:=ActiveSheet
ActiveSheet.Name = newShtName
[a5] = newShtName 'new line
Sheets(shtName).Activate
Else
MsgBox "You have already created this week.", vbCritical
End If
End Sub

 

[Jock]

Marvellous.
I have noticed though, that the formulae I have in 'hidden' columns are not
copied over. Is there a way around this?
Thanks

--
Traa Dy Liooar

Jock

Jock, the code I posted has a mistake that will show up on subsequent runs.
Change line
[a5]=newShtName 'new line
like this:
[a5] = CDate(newShtName) 'new line
James

Sub CopyShtPlus7Days()
Dim shtName As String, newShtName As String
shtName = ActiveSheet.Name
newShtName = Format([a5] + 7, "dd-mm-yy") 'line changed
If Not SheetExists(newShtName) Then
ActiveSheet.Copy after:=ActiveSheet
ActiveSheet.Name = newShtName
[a5] = newShtName 'new line
Sheets(shtName).Activate
Else
MsgBox "You have already created this week.", vbCritical
End If
End Sub

James,
Can this code be tweaked to:
Take the date from A5 and use as new sheet tab name with 7 days added to
it
and then paste this new date in A5 in the new sheet?

Thanks,

 

[Zone]

Jock,
Sorry, I cannot recreate the problem.

I think this thread has about run its course. If you can't figure out
what's wrong, post a new message with a subject like "Copying Sheet Doesn't
Copy Hidden Formulae" or something like that and explain the problem in the
body of the message. Someone may have an idea.
Regards, James

Marvellous.
I have noticed though, that the formulae I have in 'hidden' columns are
not
copied over. Is there a way around this?
Thanks

--
Traa Dy Liooar

Jock

Jock, the code I posted has a mistake that will show up on subsequent
runs.
Change line
[a5]=newShtName 'new line
like this:
[a5] = CDate(newShtName) 'new line
James

Sub CopyShtPlus7Days()
Dim shtName As String, newShtName As String
shtName = ActiveSheet.Name
newShtName = Format([a5] + 7, "dd-mm-yy") 'line changed
If Not SheetExists(newShtName) Then
ActiveSheet.Copy after:=ActiveSheet
ActiveSheet.Name = newShtName
[a5] = newShtName 'new line
Sheets(shtName).Activate
Else
MsgBox "You have already created this week.", vbCritical
End If
End Sub

James,
Can this code be tweaked to:
Take the date from A5 and use as new sheet tab name with 7 days added
to
it
and then paste this new date in A5 in the new sheet?

Thanks,

 

[Jock]

OK, thanks for trying though.
--
Traa Dy Liooar

Jock

Jock,
Sorry, I cannot recreate the problem.

I think this thread has about run its course. If you can't figure out
what's wrong, post a new message with a subject like "Copying Sheet Doesn't
Copy Hidden Formulae" or something like that and explain the problem in the
body of the message. Someone may have an idea.
Regards, James

Marvellous.
I have noticed though, that the formulae I have in 'hidden' columns are
not
copied over. Is there a way around this?
Thanks

--
Traa Dy Liooar

Jock

Jock, the code I posted has a mistake that will show up on subsequent
runs.
Change line
[a5]=newShtName 'new line
like this:
[a5] = CDate(newShtName) 'new line
James

Sub CopyShtPlus7Days()
Dim shtName As String, newShtName As String
shtName = ActiveSheet.Name
newShtName = Format([a5] + 7, "dd-mm-yy") 'line changed
If Not SheetExists(newShtName) Then
ActiveSheet.Copy after:=ActiveSheet
ActiveSheet.Name = newShtName
[a5] = newShtName 'new line
Sheets(shtName).Activate
Else
MsgBox "You have already created this week.", vbCritical
End If
End Sub

James,
Can this code be tweaked to:
Take the date from A5 and use as new sheet tab name with 7 days added
to
it
and then paste this new date in A5 in the new sheet?

Thanks,