With the macro shown below, which works ok, the copied sheet
using the Copy before method, always go back to the start of the Worksheets,
which has
now got 16 sheets in the workbook and increases each month.
For the sake of convenience it would be nice for it to go back 1 worksheet
only.
ie before CurrentMasterHours worksheet.
Help please
Jim


<<<Dim Location
On Error Resume Next
Worksheets("CurrentMasterHours").Select
Location = InputBox("Enter Archive Name to be Stored, eg Sept '99",
"Time Sheets - Archival Transfer")
If Location = "" Then
Exit Sub
Else
Sheets("CurrentMasterHours").Copy befo=Sheets(1)
Sheets(1).Name = Location
End If
On Error GoTo 0
End Sub

Jim,

Sheets("CurrentMasterHours").Copy befo=Sheets("CurrentMasterHours")
ActiveSheet.Name = Location

HTH
Henry


"Jim" wrote in message
. ..
With the macro shown below, which works ok, the copied sheet
using the Copy before method, always go back to the start of the
Worksheets,
which has
now got 16 sheets in the workbook and increases each month.
For the sake of convenience it would be nice for it to go back 1 worksheet
only.
ie before CurrentMasterHours worksheet.
Help please
Jim


<<<Dim Location
On Error Resume Next
Worksheets("CurrentMasterHours").Select
Location = InputBox("Enter Archive Name to be Stored, eg Sept
'99",
"Time Sheets - Archival Transfer")
If Location = "" Then
Exit Sub
Else
Sheets("CurrentMasterHours").Copy befo=Sheets(1)
Sheets(1).Name = Location
End If
On Error GoTo 0
End Sub

Jim,

Change the line

Sheets("CurrentMasterHours").Copy befo=Sheets(1)

to

Sheets("CurrentMasterHours").Copy
befo=workSheets(("CurrentMasterHours")


not tested, but should work

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Jim" wrote in message
. ..
With the macro shown below, which works ok, the copied sheet
using the Copy before method, always go back to the start of the
Worksheets,
which has
now got 16 sheets in the workbook and increases each month.
For the sake of convenience it would be nice for it to go back 1 worksheet
only.
ie before CurrentMasterHours worksheet.
Help please
Jim


<<<Dim Location
On Error Resume Next
Worksheets("CurrentMasterHours").Select
Location = InputBox("Enter Archive Name to be Stored, eg Sept
'99",
"Time Sheets - Archival Transfer")
If Location = "" Then
Exit Sub
Else
Sheets("CurrentMasterHours").Copy befo=Sheets(1)
Sheets(1).Name = Location
End If
On Error GoTo 0
End Sub

Sheets("CurrentMasterHours").Copy befo=Sheets(sheets.count)

--
Regards,
Tom Ogilvy


Jim wrote in message
. ..
With the macro shown below, which works ok, the copied sheet
using the Copy before method, always go back to the start of the
Worksheets,
which has
now got 16 sheets in the workbook and increases each month.
For the sake of convenience it would be nice for it to go back 1 worksheet
only.
ie before CurrentMasterHours worksheet.
Help please
Jim


<<<Dim Location
On Error Resume Next
Worksheets("CurrentMasterHours").Select
Location = InputBox("Enter Archive Name to be Stored, eg Sept
'99",
"Time Sheets - Archival Transfer")
If Location = "" Then
Exit Sub
Else
Sheets("CurrentMasterHours").Copy befo=Sheets(1)
Sheets(1).Name = Location
End If
On Error GoTo 0
End Sub

Many thanks Henry, works just fine
Jim

"Henry" wrote in message
...
Jim,

Sheets("CurrentMasterHours").Copy befo=Sheets("CurrentMasterHours")
ActiveSheet.Name = Location

HTH
Henry


"Jim" wrote in message
. ..
With the macro shown below, which works ok, the copied sheet
using the Copy before method, always go back to the start of the
Worksheets,
which has
now got 16 sheets in the workbook and increases each month.
For the sake of convenience it would be nice for it to go back 1
worksheet
only.
ie before CurrentMasterHours worksheet.
Help please
Jim


<<<Dim Location
On Error Resume Next
Worksheets("CurrentMasterHours").Select
Location = InputBox("Enter Archive Name to be Stored, eg Sept
'99",
"Time Sheets - Archival Transfer")
If Location = "" Then
Exit Sub
Else
Sheets("CurrentMasterHours").Copy befo=Sheets(1)
Sheets(1).Name = Location
End If
On Error GoTo 0
End Sub

Hi Bob
Many thanks, had to change
Sheets(1).Name = Location
to
ActiveSheet.Name = Location
because it generated a second CurrentMasterHours Sheet(2)!!
Henrys code worked fine.
Many thanks for your help.
Jim

"Bob Phillips" wrote in message
...
Jim,

Change the line

Sheets("CurrentMasterHours").Copy befo=Sheets(1)

to

Sheets("CurrentMasterHours").Copy
befo=workSheets(("CurrentMasterHours")


not tested, but should work

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Jim" wrote in message
. ..
With the macro shown below, which works ok, the copied sheet
using the Copy before method, always go back to the start of the
Worksheets,
which has
now got 16 sheets in the workbook and increases each month.
For the sake of convenience it would be nice for it to go back 1
worksheet
only.
ie before CurrentMasterHours worksheet.
Help please
Jim


<<<Dim Location
On Error Resume Next
Worksheets("CurrentMasterHours").Select
Location = InputBox("Enter Archive Name to be Stored, eg Sept
'99",
"Time Sheets - Archival Transfer")
If Location = "" Then
Exit Sub
Else
Sheets("CurrentMasterHours").Copy befo=Sheets(1)
Sheets(1).Name = Location
End If
On Error GoTo 0
End Sub

Hi Tom
Sadly, it didn't work, it generated a second copy of CurrentMasterHours(2)
and also no archived sheet.
Henrys solution worked fine.
Many thanks
Jim
PS I do like one liners very much though!!


"Tom Ogilvy" wrote in message
...
Sheets("CurrentMasterHours").Copy befo=Sheets(sheets.count)

--
Regards,
Tom Ogilvy


Jim wrote in message
. ..
With the macro shown below, which works ok, the copied sheet
using the Copy before method, always go back to the start of the
Worksheets,
which has
now got 16 sheets in the workbook and increases each month.
For the sake of convenience it would be nice for it to go back 1
worksheet
only.
ie before CurrentMasterHours worksheet.
Help please
Jim


<<<Dim Location
On Error Resume Next
Worksheets("CurrentMasterHours").Select
Location = InputBox("Enter Archive Name to be Stored, eg Sept
'99",
"Time Sheets - Archival Transfer")
If Location = "" Then
Exit Sub
Else
Sheets("CurrentMasterHours").Copy befo=Sheets(1)
Sheets(1).Name = Location
End If
On Error GoTo 0
End Sub

All code that copies the CurrentMasterHours sheet will initially produce
CurrentMasterHours(2) (or a numerical variant if there are other similarly
named sheets) until you rename it. So does Henry's solution. You weren't
aware of this????

Mine was obviously the worst solution because I took your description
literally and missed the "I.E." as to what you meant by one sheet back. Not
sure what you mean by no archived sheet - you we able to figure out how to
rename the sheet with Bob's code, but not mine (LOL). One copy command, One
copy of the sheet produced - same as Henry and Bob's solution and your
original code - only difference is the location.

--
Regards,
Tom Ogilvy




Jim wrote in message
...
Hi Tom
Sadly, it didn't work, it generated a second copy of CurrentMasterHours(2)
and also no archived sheet.
Henrys solution worked fine.
Many thanks
Jim
PS I do like one liners very much though!!


"Tom Ogilvy" wrote in message
...
Sheets("CurrentMasterHours").Copy befo=Sheets(sheets.count)

--
Regards,
Tom Ogilvy


Jim wrote in message
. ..
With the macro shown below, which works ok, the copied sheet
using the Copy before method, always go back to the start of the
Worksheets,
which has
now got 16 sheets in the workbook and increases each month.
For the sake of convenience it would be nice for it to go back 1
worksheet
only.
ie before CurrentMasterHours worksheet.
Help please
Jim


<<<Dim Location
On Error Resume Next
Worksheets("CurrentMasterHours").Select
Location = InputBox("Enter Archive Name to be Stored, eg Sept
'99",
"Time Sheets - Archival Transfer")
If Location = "" Then
Exit Sub
Else
Sheets("CurrentMasterHours").Copy befo=Sheets(1)
Sheets(1).Name = Location
End If
On Error GoTo 0
End Sub

Hi Tom
Yes, if you look at the original code, you can see how I renamed the Sheet,
Henrys solution worked fine, but yours fell over in the rename of worksheet
event I suspect.
Thanks for your continuing interest.
Have reproduced my original code for your perusal.
Perhaps there is a more efficient line or two of code to do what I want?
Thoughts always appreciated, as I am still learning !!!!!
Regards
Jim

<<<Dim Location
On Error Resume Next
Worksheets("CurrentMasterHours").Select
Location = InputBox("Enter Archive Name to be Stored, eg Sept '99",
"Time Sheets - Archival Transfer")
If Location = "" Then
Exit Sub
Else
Sheets("CurrentMasterHours").Copy befo=Sheets(1)
Sheets(1).Name = Location
End If
On Error GoTo 0
End Sub






"Tom Ogilvy" wrote in message
...
All code that copies the CurrentMasterHours sheet will initially produce
CurrentMasterHours(2) (or a numerical variant if there are other similarly
named sheets) until you rename it. So does Henry's solution. You weren't
aware of this????

Mine was obviously the worst solution because I took your description
literally and missed the "I.E." as to what you meant by one sheet back.
Not
sure what you mean by no archived sheet - you we able to figure out how to
rename the sheet with Bob's code, but not mine (LOL). One copy command,
One
copy of the sheet produced - same as Henry and Bob's solution and your
original code - only difference is the location.

--
Regards,
Tom Ogilvy




Jim wrote in message
...
Hi Tom
Sadly, it didn't work, it generated a second copy of
CurrentMasterHours(2)
and also no archived sheet.
Henrys solution worked fine.
Many thanks
Jim
PS I do like one liners very much though!!


"Tom Ogilvy" wrote in message
...
Sheets("CurrentMasterHours").Copy befo=Sheets(sheets.count)

--
Regards,
Tom Ogilvy


Jim wrote in message
. ..
With the macro shown below, which works ok, the copied sheet
using the Copy before method, always go back to the start of the
Worksheets,
which has
now got 16 sheets in the workbook and increases each month.
For the sake of convenience it would be nice for it to go back 1
worksheet
only.
ie before CurrentMasterHours worksheet.
Help please
Jim


<<<Dim Location
On Error Resume Next
Worksheets("CurrentMasterHours").Select
Location = InputBox("Enter Archive Name to be Stored, eg
Sept
'99",
"Time Sheets - Archival Transfer")
If Location = "" Then
Exit Sub
Else
Sheets("CurrentMasterHours").Copy befo=Sheets(1)
Sheets(1).Name = Location
End If
On Error GoTo 0
End Sub