Copy Sheet Problem

  • Thread starter Thread starter Jim
  • Start date Start date
J

Jim

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 before:=Sheets(1)
Sheets(1).Name = Location
End If
On Error GoTo 0
End Sub>>>
 
Jim,

Sheets("CurrentMasterHours").Copy before:=Sheets("CurrentMasterHours")
ActiveSheet.Name = Location

HTH
Henry
 
Jim,

Change the line

Sheets("CurrentMasterHours").Copy before:=Sheets(1)

to

Sheets("CurrentMasterHours").Copy
before:=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)
 
Many thanks Henry, works just fine
Jim

Henry said:
Jim,

Sheets("CurrentMasterHours").Copy before:=Sheets("CurrentMasterHours")
ActiveSheet.Name = Location

HTH
Henry
Click to expand...
 
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
 
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!!
 
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.
 
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 before:=Sheets(1)
Sheets(1).Name = Location
End If
On Error GoTo 0
End Sub>>>
 
Back
Top