Copy a Sheet to New Sheet Q
 

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

Copy a Sheet to New Sheet Q
 

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" wrote in message
ups.com...
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

Copy a Sheet to New Sheet Q
 

On Aug 20, 12:14 pm, "Zone" wrote:
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" wrote in message

ups.com...



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

Copy a Sheet to New Sheet Q
 

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

"Sean" wrote in message
ps.com...
On Aug 20, 12:14 pm, "Zone" wrote:
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" wrote in message

ups.com...



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

Copy a Sheet to New Sheet Q
 

On Aug 20, 9:54 pm, "Zone" wrote:
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

"Sean" wrote in message

ps.com...



On Aug 20, 12:14 pm, "Zone" wrote:
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" wrote in message

roups.com...

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

Copy a Sheet to New Sheet Q
 

Change the ActiveSheet.Copy line like this:

ActiveSheet.Copy after:=ActiveSheet


"Sean" wrote in message
ps.com...
On Aug 20, 9:54 pm, "Zone" wrote:
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

Copy a Sheet to New Sheet Q
 

On Aug 21, 11:41 am, "Zone" wrote:
Change the ActiveSheet.Copy line like this:

ActiveSheet.Copy after:=ActiveSheet

"Sean" wrote in message

ps.com...



On Aug 20, 9:54 pm, "Zone" wrote:
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- Hide quoted text -

- Show quoted text -

Thanks for your help James

Copy a Sheet to New Sheet Q
 

On Aug 21, 12:55 pm, Sean wrote:
On Aug 21, 11:41 am, "Zone" wrote:





Change the ActiveSheet.Copy line like this:

ActiveSheet.Copy after:=ActiveSheet

"Sean" wrote in message

ups.com...

On Aug 20, 9:54 pm, "Zone" wrote:
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- Hide quoted text -

- Show quoted text -

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

Copy a Sheet to New Sheet Q
 

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

Copy a Sheet to New Sheet Q
 

On Aug 21, 6:25 pm, "Zone" wrote:
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

Copy a Sheet to New Sheet Q
 

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

"Sean" wrote in message
ups.com...
On Aug 21, 6:25 pm, "Zone" wrote:
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

Copy a Sheet to New Sheet Q
 

On Aug 21, 8:15 pm, "Zone" wrote:
Did you copy the function from my post and paste it in the same module as
the subroutine?

"Sean" wrote in message

ups.com...



On Aug 21, 6:25 pm, "Zone" wrote:
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

Copy a Sheet to New Sheet Q
 

On Aug 21, 8:15 pm, "Zone" wrote:
Did you copy the function from my post and paste it in the same module as
the subroutine?

"Sean" wrote in message

ups.com...



On Aug 21, 6:25 pm, "Zone" wrote:
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

Copy a Sheet to New Sheet Q
 

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

"Sean" wrote in message
oups.com...
On Aug 21, 8:15 pm, "Zone" wrote:
Did you copy the function from my post and paste it in the same module as
the subroutine?

"Sean" wrote in message

ups.com...



On Aug 21, 6:25 pm, "Zone" wrote:
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

Copy a Sheet to New Sheet Q
 

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,

--
tia

Copy a Sheet to New Sheet Q
 

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

"JockW" wrote in message
...
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,

--
tia

Copy a Sheet to New Sheet Q
 

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

"Zone" wrote in message
...
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

"JockW" wrote in message
...
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,

--
tia

Copy a Sheet to New Sheet Q
 

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


"Zone" wrote:

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

"Zone" wrote in message
...
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

"JockW" wrote in message
...
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,

--
tia

Copy a Sheet to New Sheet Q
 

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

"Jock" wrote in message
...
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


"Zone" wrote:

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

"Zone" wrote in message
...
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

"JockW" wrote in message
...
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,

--
tia

Copy a Sheet to New Sheet Q
 

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

Jock


"Zone" wrote:

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

"Jock" wrote in message
...
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


"Zone" wrote:

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

"Zone" wrote in message
...
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

"JockW" wrote in message
...
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,

--
tia