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 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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>

On Aug 20, 12:14 pm, "Zone" <KingOfWhi...@aol.com> 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" <seanrya...@yahoo.co.uk> wrote in message
>
> news:(E-Mail Removed)...
>
>
>
> > 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, 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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> On Aug 20, 12:14 pm, "Zone" <KingOfWhi...@aol.com> 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" <seanrya...@yahoo.co.uk> wrote in message
>>
>> news:(E-Mail Removed)...
>>
>>
>>
>> > 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
>

On Aug 20, 9:54 pm, "Zone" <KingOfWhi...@aol.com> 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" <seanrya...@yahoo.co.uk> wrote in message
>
> news:(E-Mail Removed)...
>
>
>
> > On Aug 20, 12:14 pm, "Zone" <KingOfWhi...@aol.com> 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" <seanrya...@yahoo.co.uk> wrote in message
>
> >>news:(E-Mail Removed)...
>
> >> > 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

Change the ActiveSheet.Copy line like this:

ActiveSheet.Copy after:=ActiveSheet


"Sean" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> On Aug 20, 9:54 pm, "Zone" <KingOfWhi...@aol.com> 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
>

On Aug 21, 11:41 am, "Zone" <KingOfWhi...@aol.com> wrote:
> Change the ActiveSheet.Copy line like this:
>
> ActiveSheet.Copy after:=ActiveSheet
>
> "Sean" <seanrya...@yahoo.co.uk> wrote in message
>
> news:(E-Mail Removed)...
>
>
>
> > On Aug 20, 9:54 pm, "Zone" <KingOfWhi...@aol.com> 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

On Aug 21, 12:55 pm, Sean <seanrya...@yahoo.co.uk> wrote:
> On Aug 21, 11:41 am, "Zone" <KingOfWhi...@aol.com> wrote:
>
>
>
>
>
> > Change the ActiveSheet.Copy line like this:
>
> > ActiveSheet.Copy after:=ActiveSheet
>
> > "Sean" <seanrya...@yahoo.co.uk> wrote in message
>
> >news:(E-Mail Removed)...
>
> > > On Aug 20, 9:54 pm, "Zone" <KingOfWhi...@aol.com> 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

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
>

On Aug 21, 6:25 pm, "Zone" <KingOfWhi...@aol.com> 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