Thanks OssieMac,

It's been quite an experience. I found a useful formula to deal with the
worksheet protection issue I thought you may find useful.

Dim ws As Worksheet
Application.ScreenUpdating = False

For Each ws In ActiveWorkbook.Worksheets

If ws.ProtectContents = False Then
ws.Protect ("123")
End If

Next

Application.ScreenUpdating = True

Many thanks
Jorge

"OssieMac" wrote:

Hi Again Jorge,

In answer to your question, Im a 3rd generation Aussie of Scottish decent
and hence OssieMac and I live in Brisbane.

Below is the modified code. I am uncertain whether you intend changing the
value of cell K3 on the original sheet or if you simply want to create the
new sheet with that date + 14. If possible, I would suggest that you do NOT
change it on the original sheet because if you do then it will be possible to
inadvertently run the macro again from that sheet with a sheet name date
which is then 2 X 14 days ahead. By not changing it on the original sheet,
the validation in the macro will prevent multiple sheets 2X, 3X etc
fortnights ahead being created from the same original. However, if you have
to change it, then the code is in comments in the macro.

If you do not change the date in cell K3on the original sheet, then the old
value will be copied to the new sheet but then there is code at the bottom of
the macro to update cell K3 in the new sheet to the old value +14. You just
need to remove the single quote to activate the line of code. This is the
preferred method.

The code you have added appears to have been recorded so I assume that you
are competent in that area. I suggest that you simply record a macro for the
freeze panes and insert it at the bottom of the code. Same for the sheet
protection.

NOTE: When selecting a range whether it is a single cell or range of cells,
if after selecting the first range, you hold the Ctrl key down you can select
further ranges including non adjacent ones then all of the cells remain
selected and you can perform actions on the entire selection. Worth knowing
for protect/unprotect.

You will need to re-insert your code to the bottom of this.

Sub Insert_New_Sheet()
Dim oldShtName As String
Dim newShtName As String
Dim wSht As Object

oldShtName = ActiveSheet.Name

'Create string variable from date in
'Active Sheet cell K3 + 14 days
newShtName = Format(ActiveSheet.Range("K3") _
+ 14, "d-mm-yyyy")

'Test that new sheet name not previously created.
For Each wSht In Sheets
If LCase(wSht.Name) = LCase(newShtName) Then
MsgBox "Worksheet " & newShtName & _
" already exists." & Chr(13) & _
"Processing terminated"
End
End If
Next wSht

'If cell K3 in the old sheet is to be updated
'with the + 14 days then take the single quote _
'off the following line. (See comment at end also.)
'Sheets(oldShtName).Range("K3") = ActiveSheet.Range("K3") + 14

'Following line adds sheet as first sheet
Sheets.Add Befo=Sheets(1)

'Following line adds sheet before active sheet
'Sheets.Add Befo=Sheets(oldShtName)

'Following line adds sheet after active sheet
'Sheets.Add After:=Sheets(oldShtName)

'Following line adds sheet after last sheet
'Sheets.Add After:=Sheets(Sheets.Count)

ActiveSheet.Name = newShtName
Sheets(oldShtName).Cells.Copy
Sheets(newShtName).Paste

Sheets(oldShtName).Range("B34").Copy
Sheets(newShtName).Select
Range("B21").Select
ActiveSheet.Paste Link:=True

Application.CutCopyMode = False

'If you updated the date in cell K3
'in the original sheet above with the +14
'then it will have been copied with the update
'to the new sheet. However, if you did not
'include it above but want it updated in the
'new sheet then remove the single quote from _
'the following line.
'Sheets(newShtName).Range("K3") = _
Sheets(newShtName).Range("K3") + 14

Range("A1").Select

End Sub

Regards,

OssieMac

"JorgeG.ACT" wrote:

Hi OssieMac,

To improve your perspective, I'm working with are staff time sheets. As you
would have worked out, the idea is to have multiple time sheets in the one
workbook rather than opening a new workbook for each time sheet.

As such all the sheets are identical in format but obviously the hours
worked etc are going to be different. This means that the last [active] sheet
is effectively the master sheet, ie the sheet upon which the new sheet will
be based. Hope I'm being clear enough.

Yes I agree that there is no longer the need for user input concerning the
sheet name and you are also correct regarding the date format, though would
you make it d-mm-yyyy.

On a more personal note (if you don't mind) you know where I'm from, where
to you hail from and is the "Mac" bit to do with your actual name or the fact
that you use Apple Macs?

Regards
Jorge


"OssieMac" wrote:

Hi Jorge,

I need a little more information. Is the worksheet from which the copied
sheets are created always the same sheet like a master sheet? If it is then
what is the master sheet name because it is adviseable to ensure the macro
copies that sheet and not some other sheet which just happens to be the
active sheet.

Also, if the new sheet is to be created from a date on the original, my
thoughts are that we discard the input by the user for sheet name and
automate the creation of the new sheet name. It should not then be necessary
to validate the characters used because the program will control it. (Easy to
do). From the info you gave me earlier with a sheet name as 31-5-07, it
appears that you are in an area that uses d m y date format. (In fact the
Suffix on your handle indicates to me you might be in ACT Aussie land).
However please confirm the format for the date for the new sheet name. (ie.
dd-mm-yy).

Regards,

OssieMac



"OssieMac" wrote:

Hi Jorge,

This communities site has not been picking up any new postings for a couple
of days and I guess that is why you have posted 3 times. Anyway I have only
just got your request. I am sure I can do what you want so I'll get back to
you.

Regards,

OssieMac

"JorgeG.ACT" wrote:

Thanks OssieMac,
It's working terrifically. However, there are a few more things I'd like to
try if you are willing and able:
1. Increase the date in cell K3 by 14 days every time the macro is run;
2. use this new date to name the new sheet;
3. Increase the sheet name validation to deal with the characters: : / \ ? *
' [ ] or a name with greater than 31 characters.

Regards
Jorge

"OssieMac" wrote:

OK Jorge here is another copy of the macro. I have included the link setting
code on the bottom and have also done something that I should have done
before. I have included some validation for the new sheet name so that the
procedure aborts if the user click OK without a new sheet name or the user
clicks cancel.

Sub Insert_New_Sheet()
Dim oldShtName As String
Dim newShtName As String
Dim wSht As Object
Dim wShtExists As Boolean
Dim inputPrompt As String

oldShtName = ActiveSheet.Name

inputPrompt = "Enter name for new sheet or Cancel to exit."

Do
wShtExists = False
Beep
newShtName = InputBox(prompt:=inputPrompt, _
Title:="New Sheet Name")

'Next few lines of code required in case user Cancels
'or user only enters only spaces in the input box.
'or user enters nothing and clicks on OK.
If Len(Trim(newShtName)) = 0 Then
Beep
MsgBox "Invalid entry or user Cancelled." _
& Chr(13) & Chr(13) & "New worksheet not created."
End
End If

For Each wSht In Sheets
If LCase(wSht.Name) = LCase(newShtName) Then
wShtExists = True
inputPrompt = _
"Worksheet name already exists. Enter new name"
End If
Next wSht
Loop While wShtExists = True

'Following line adds sheet as first sheet
Sheets.Add Befo=Sheets(1)

'Following line adds sheet before active sheet
'Sheets.Add Befo=Sheets(oldShtName)

'Following line adds sheet after active sheet
'Sheets.Add After:=Sheets(oldShtName)

'Following line adds sheet after last sheet
'Sheets.Add After:=Sheets(Sheets.Count)

ActiveSheet.Name = newShtName
Sheets(oldShtName).Cells.Copy
Sheets(newShtName).Paste

Sheets(oldShtName).Range("B34").Copy
Sheets(newShtName).Select
Range("B21").Select
ActiveSheet.Paste Link:=True

Application.CutCopyMode = False
Range("A1").Select

End Sub

Regards,

OssieMac

"JorgeG.ACT" wrote:

Sorry OssieMac,

What I meant to say below was not that the information had been copied but
that the new sheet had to before manually copied, before the macro, which
cleared details from that new sheet could be run.

Regards
Jorgeg.ACT

"JorgeG.ACT" wrote:

Your Welcome OssieMac,
It's great to get someone who not only knows what they are doing but is
prepared to actually help.To fill you in a little. Firstly, the spreadheet is
used by many staff members, so its not simply my limitations I'm dealing
with here.

I had a macro which was "crudely" created using the Macro recording
facility. It cleared information from the new sheet which is manually copied;
clearly Macros created this way have limitations hence my request for
assistance as I'm trying to make this process as user friendly as possible.
Thats where your macro has made a real improvement.

Currently there are only references to other cells on the same sheet, but no
links between sheets. What I need is for cell B21 in the new sheet to link
back to field B34 in the old sheet. Currently the name of the sheet is a date
such as 30-5-07 hence the paste link formula being as follows:
='30-5-07'!$B$34.

By the way I have created tool bar buttons to run macros before, so that is
one less thing you will need to concentrate on.

Regards
Jorgeg.ACT

"OssieMac" wrote:

Thanks for the kudos. Its nice to have a win sometimes so you have made my
day. Flattery gets you everywhere so if you can give me the following
details, I will attempt to do the link thing for you but it is dependent on
just what is needed so if you can provide the following info I will see what
I can do.
Does the link already exist on the old sheet and not copying properly?
Does it link back to the old sheet or to something else?
Which cell reference on the new sheet for the link?