Macro that can switch between tabs

[Guest]

I wish to create a macro that basically creates a new tab and copies the
information from the old tab to the new tab.

The issue is that the tab names will vary between different users and the
number of tabs will increase regularly.

I have very limited experience with visual basic so every assistance would
be greatly appreciated.

 

[Guest]

Can be done. Where do you want the new sheets inserted? Before first sheet,
before or after the active sheet or at the end.

I assume that you want the user to be able to input a name for the sheet.
The system will be able to check if the name exists and if so, request
another name form the user.

Regards,

OssieMac

 

[Guest]

I didn’t wait for your answer to my question. However, see what this does for
you.
The following code adds a worksheet to an active workbook and copies the
active work sheet to the new work sheet.
Because you said that you have limited experience with Visual Basic, I will
step through what you need to do.
Ensure that you have a backup copy of your workbook in case the macro does
not do exactly what you want.
The macro is designed to be run from the active sheet in the workbook and
this is the one it copies to the new sheet.

Shift/F11 will open the VBA Editor.
Select the menu item Insert->Module.
Copy the code into the module.

A single quote is used to mark text as a remark and the text should be
green. (This text is ignored by the program.)
If there are any lines in red then it is probably because the lines ended up
with a line feed between when I posted the code and you copied it into the
VBA editor. Use the delete key at the end of the first red line in the group
and bring the following red lines up into one line and when you move off the
line it should be black (with a bit of blue).

You will see some code with comments that gives you options on where you
want the new worksheet to be placed. As I have posted the code, the only
active one is the first one which places the worksheet before the first
existing worksheet. Only one of these lines is to be active but you can
change where you want the worksheet by placing a single quote in front of the
existing active one and remove the single quote from one of the others.

Close the VBA Editor and then save the workbook. If you are working in
xl2007 then save as a macro enabled workbook.

To run the macro select from the workbook menu items. If the macro does what
you want, then I will think about a toolbar button for you to run it but let
us get it doing what you want first.

All users will need to have options set to ‘macro enabled’. Look up help on
this if you are unsure what to do here.

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"

Do
wShtExists = False
Beep
newShtName = InputBox(prompt:=inputPrompt, _
Title:="New Sheet Name")
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 Before:=Sheets(1)

'Following line adds sheet before active sheet
'Sheets.Add Before:=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
Application.CutCopyMode = False
Range("A1").Select

End Sub

Regards,

OssieMac

 

[Guest]

Thanks OssieMac,

It worked brilliently, very impressive indeed.

One more thing though, one particular cell I would like to be paste linked.
Is that possible?

 

[Guest]

Thanks for the kudos. It’s 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?
What is sheet name and cell reference to which it is linked?
Can you create the link manually? If so, then create it and then select the
cell and then on the formula bar at the top, highlight the formula and then
copy it and then paste it into a posting here.

Regards,

OssieMac

 

[Guest]

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 it’s 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.
That’s 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

 

[Guest]

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

 

[Guest]

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 Before:=Sheets(1)

'Following line adds sheet before active sheet
'Sheets.Add Before:=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

 

[Guest]

This is truly excellent. I've added some of my own instructions after yours
to enable the clearing of parts of the form. (See Below). I don't wish to
stretch the friendship here so I ask the following more out of curiosity to
learn than anything else. Would you b e able to:
1. Include in your validation rule for the name of the new sheet to preclude
the use of any of the following characters: :\ / ? * [ ] or is more than 31
characters;
2. Enable the retention of the Freeze Pane and the Sheet Protection in the
new sheet; and
3. Have the Date in cell K3 to be made the name of the new "Sheet".

Regards
Jorge

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 Before:=Sheets(1)

'Following line adds sheet before active sheet
'Sheets.Add Before:=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

Range("D40").Select
Selection.Copy
Range("B21").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
ActiveWindow.SmallScroll Down:=-15
Range("K3").Select
Application.CutCopyMode = False
Selection.Copy
Range("J40").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("B5:K13").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("B17:K20").Select
Selection.ClearContents
Range("F32:G32").Select
Selection.ClearContents
Range("K1").Select
Selection.ClearContents
Range("B5").Select

 

[Guest]

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: : / \ ? * ' [ ] or a
name with greater than 31 characters.

Regards
Jorge

 

[Guest]

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

 

[Guest]

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

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

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 Before:=Sheets(1)

'Following line adds sheet before active sheet
'Sheets.Add Before:=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

 

[Guest]

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

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

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

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 Before:=Sheets(1)

'Following line adds sheet before active sheet
'Sheets.Add Before:=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

:

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

:

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 it’s 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.
That’s 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

:

Thanks for the kudos. It’s 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?
What is sheet name and cell reference to which it is linked?
Can you create the link manually? If so, then create it and then select the
cell and then on the formula bar at the top, highlight the formula and then
copy it and then paste it into a posting here.

Regards,

OssieMac


:

Thanks OssieMac,

It worked brilliently, very impressive indeed.

One more thing though, one particular cell I would like to be paste linked.
Is that possible?


:

I didn’t wait for your answer to my question. However, see what this does for
you.
The following code adds a worksheet to an active workbook and copies the
active work sheet to the new work sheet.
Because you said that you have limited experience with Visual Basic, I will
step through what you need to do.
Ensure that you have a backup copy of your workbook in case the macro does
not do exactly what you want.
The macro is designed to be run from the active sheet in the workbook and
this is the one it copies to the new sheet.

Shift/F11 will open the VBA Editor.
Select the menu item Insert->Module.
Copy the code into the module.

A single quote is used to mark text as a remark and the text should be
green. (This text is ignored by the program.)
If there are any lines in red then it is probably because the lines ended up
with a line feed between when I posted the code and you copied it into the
VBA editor. Use the delete key at the end of the first red line in the group
and bring the following red lines up into one line and when you move off the
line it should be black (with a bit of blue).

You will see some code with comments that gives you options on where you
want the new worksheet to be placed. As I have posted the code, the only
active one is the first one which places the worksheet before the first
existing worksheet. Only one of these lines is to be active but you can
change where you want the worksheet by placing a single quote in front of the
existing active one and remove the single quote from one of the others.

Close the VBA Editor and then save the workbook. If you are working in
xl2007 then save as a macro enabled workbook.

To run the macro select from the workbook menu items. If the macro does what
you want, then I will think about a toolbar button for you to run it but let
us get it doing what you want first.

All users will need to have options set to ‘macro enabled’. Look up help on
this if you are unsure what to do here.

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"

Do
wShtExists = False
Beep
newShtName = InputBox(prompt:=inputPrompt, _
Title:="New Sheet Name")
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 Before:=Sheets(1)

'Following line adds sheet before active sheet
'Sheets.Add Before:=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
Application.CutCopyMode = False
Range("A1").Select

End Sub

Regards,

OssieMac

:

Can be done. Where do you want the new sheets inserted? Before first sheet,
before or after the active sheet or at the end.

I assume that you want the user to be able to input a name for the sheet.
The system will be able to check if the name exists and if so, request
another name form the user.

Regards,

OssieMac

:

I wish to create a macro that basically creates a new tab and copies the
information from the old tab to the new tab.

The issue is that the tab names will vary between different users and the
number of tabs will increase regularly.

I have very limited experience with visual basic so every assistance would
be greatly appreciated.

 

[Guest]

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

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

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

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

:

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 Before:=Sheets(1)

'Following line adds sheet before active sheet
'Sheets.Add Before:=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

:

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

:

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 it’s 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.
That’s 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

:

Thanks for the kudos. It’s 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?
What is sheet name and cell reference to which it is linked?
Can you create the link manually? If so, then create it and then select the
cell and then on the formula bar at the top, highlight the formula and then
copy it and then paste it into a posting here.

Regards,

OssieMac


:

Thanks OssieMac,

It worked brilliently, very impressive indeed.

One more thing though, one particular cell I would like to be paste linked.
Is that possible?


:

I didn’t wait for your answer to my question. However, see what this does for
you.
The following code adds a worksheet to an active workbook and copies the
active work sheet to the new work sheet.
Because you said that you have limited experience with Visual Basic, I will
step through what you need to do.
Ensure that you have a backup copy of your workbook in case the macro does
not do exactly what you want.
The macro is designed to be run from the active sheet in the workbook and
this is the one it copies to the new sheet.

Shift/F11 will open the VBA Editor.
Select the menu item Insert->Module.
Copy the code into the module.

A single quote is used to mark text as a remark and the text should be
green. (This text is ignored by the program.)
If there are any lines in red then it is probably because the lines ended up
with a line feed between when I posted the code and you copied it into the
VBA editor. Use the delete key at the end of the first red line in the group
and bring the following red lines up into one line and when you move off the
line it should be black (with a bit of blue).

You will see some code with comments that gives you options on where you
want the new worksheet to be placed. As I have posted the code, the only
active one is the first one which places the worksheet before the first
existing worksheet. Only one of these lines is to be active but you can
change where you want the worksheet by placing a single quote in front of the
existing active one and remove the single quote from one of the others.

Close the VBA Editor and then save the workbook. If you are working in
xl2007 then save as a macro enabled workbook.

To run the macro select from the workbook menu items. If the macro does what
you want, then I will think about a toolbar button for you to run it but let
us get it doing what you want first.

All users will need to have options set to ‘macro enabled’. Look up help on
this if you are unsure what to do here.

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"

Do
wShtExists = False
Beep
newShtName = InputBox(prompt:=inputPrompt, _
Title:="New Sheet Name")
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 Before:=Sheets(1)

'Following line adds sheet before active sheet
'Sheets.Add Before:=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
Application.CutCopyMode = False
Range("A1").Select

End Sub

Regards,

OssieMac

:

Can be done. Where do you want the new sheets inserted? Before first sheet,
before or after the active sheet or at the end.

I assume that you want the user to be able to input a name for the sheet.
The system will be able to check if the name exists and if so, request
another name form the user.

Regards,

OssieMac

:

I wish to create a macro that basically creates a new tab and copies the
information from the old tab to the new tab.

 

[Guest]

Hi Again Jorge,

In answer to your question, I’m 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 Before:=Sheets(1)

'Following line adds sheet before active sheet
'Sheets.Add Before:=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

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

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

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

:

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

:

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 Before:=Sheets(1)

'Following line adds sheet before active sheet
'Sheets.Add Before:=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

:

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

:

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 it’s 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.
That’s 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

:

Thanks for the kudos. It’s 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?
What is sheet name and cell reference to which it is linked?
Can you create the link manually? If so, then create it and then select the
cell and then on the formula bar at the top, highlight the formula and then
copy it and then paste it into a posting here.

Regards,

OssieMac


:

Thanks OssieMac,

It worked brilliently, very impressive indeed.

One more thing though, one particular cell I would like to be paste linked.
Is that possible?


:

I didn’t wait for your answer to my question. However, see what this does for
you.
The following code adds a worksheet to an active workbook and copies the
active work sheet to the new work sheet.
Because you said that you have limited experience with Visual Basic, I will
step through what you need to do.
Ensure that you have a backup copy of your workbook in case the macro does
not do exactly what you want.
The macro is designed to be run from the active sheet in the workbook and
this is the one it copies to the new sheet.

Shift/F11 will open the VBA Editor.
Select the menu item Insert->Module.
Copy the code into the module.

A single quote is used to mark text as a remark and the text should be
green. (This text is ignored by the program.)
If there are any lines in red then it is probably because the lines ended up
with a line feed between when I posted the code and you copied it into the
VBA editor. Use the delete key at the end of the first red line in the group
and bring the following red lines up into one line and when you move off the
line it should be black (with a bit of blue).

You will see some code with comments that gives you options on where you
want the new worksheet to be placed. As I have posted the code, the only
active one is the first one which places the worksheet before the first
existing worksheet. Only one of these lines is to be active but you can
change where you want the worksheet by placing a single quote in front of the
existing active one and remove the single quote from one of the others.

Close the VBA Editor and then save the workbook. If you are working in
xl2007 then save as a macro enabled workbook.

To run the macro select from the workbook menu items. If the macro does what
you want, then I will think about a toolbar button for you to run it but let
us get it doing what you want first.

All users will need to have options set to ‘macro enabled’. Look up help on
this if you are unsure what to do here.

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"

Do
wShtExists = False
Beep
newShtName = InputBox(prompt:=inputPrompt, _
Title:="New Sheet Name")
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 Before:=Sheets(1)

'Following line adds sheet before active sheet
'Sheets.Add Before:=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
Application.CutCopyMode = False

 

[Guest]

Hi OssieMac,

I created a button to assign the macro as away of passing it on to other
users. However, what I noticed was that I needed to select unlooked (in
format control) so that the button would be copied over to the new sheet.

Is there any way of leaving the button locked so that it can not be deleted
yet still be available on the new sheet?

Regards
Jorge

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

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

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

:

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

:

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 Before:=Sheets(1)

'Following line adds sheet before active sheet
'Sheets.Add Before:=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

:

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

:

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 it’s 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.
That’s 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

:

Thanks for the kudos. It’s 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?
What is sheet name and cell reference to which it is linked?
Can you create the link manually? If so, then create it and then select the
cell and then on the formula bar at the top, highlight the formula and then
copy it and then paste it into a posting here.

Regards,

OssieMac


:

Thanks OssieMac,

It worked brilliently, very impressive indeed.

One more thing though, one particular cell I would like to be paste linked.
Is that possible?


:

I didn’t wait for your answer to my question. However, see what this does for
you.
The following code adds a worksheet to an active workbook and copies the
active work sheet to the new work sheet.
Because you said that you have limited experience with Visual Basic, I will
step through what you need to do.
Ensure that you have a backup copy of your workbook in case the macro does
not do exactly what you want.
The macro is designed to be run from the active sheet in the workbook and
this is the one it copies to the new sheet.

Shift/F11 will open the VBA Editor.
Select the menu item Insert->Module.
Copy the code into the module.

A single quote is used to mark text as a remark and the text should be
green. (This text is ignored by the program.)
If there are any lines in red then it is probably because the lines ended up
with a line feed between when I posted the code and you copied it into the
VBA editor. Use the delete key at the end of the first red line in the group
and bring the following red lines up into one line and when you move off the
line it should be black (with a bit of blue).

You will see some code with comments that gives you options on where you
want the new worksheet to be placed. As I have posted the code, the only
active one is the first one which places the worksheet before the first
existing worksheet. Only one of these lines is to be active but you can
change where you want the worksheet by placing a single quote in front of the
existing active one and remove the single quote from one of the others.

Close the VBA Editor and then save the workbook. If you are working in
xl2007 then save as a macro enabled workbook.

To run the macro select from the workbook menu items. If the macro does what
you want, then I will think about a toolbar button for you to run it but let
us get it doing what you want first.

All users will need to have options set to ‘macro enabled’. Look up help on
this if you are unsure what to do here.

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"

Do
wShtExists = False
Beep
newShtName = InputBox(prompt:=inputPrompt, _
Title:="New Sheet Name")
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 Before:=Sheets(1)

'Following line adds sheet before active sheet
'Sheets.Add Before:=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
Application.CutCopyMode = False

 

[Guest]

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

Hi Again Jorge,

In answer to your question, I’m 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 Before:=Sheets(1)

'Following line adds sheet before active sheet
'Sheets.Add Before:=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

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

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



:

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

:

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

:

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 Before:=Sheets(1)

'Following line adds sheet before active sheet
'Sheets.Add Before:=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

:

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

:

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 it’s 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.
That’s 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

:

Thanks for the kudos. It’s 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?

 

[Guest]

The only way that I know of copying the button is to unprotect the sheet,
copy it and then protect it again. Another version of the code below to show
you how to do it.

With the code you posted for protecting all worksheets I think that it still
requires you to unlock the cells that users are allowed to alter before
applying the protection. This will also apply to the newly created worksheet
and your best way is to record the unlocking. You don't have to do this to
the original again just because you unprotect it because unprotection does
not remove the unlocked info.

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

oldShtName = ActiveSheet.Name

'Unprotect so that button will copy
'Replace OssieMac with your password.
Sheets(oldShtName).Unprotect ("OssieMac")

'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 Before:=Sheets(1)

'Following line adds sheet before active sheet
'Sheets.Add Before:=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

'Reprotect the original sheet
'Replace OssieMac with your password
Sheets(oldShtName).Protect Password:="OssieMac", _
DrawingObjects:=True, _
Contents:=True, _
Scenarios:=True


End Sub

Regards,

OssieMac

Hi OssieMac,

I created a button to assign the macro as away of passing it on to other
users. However, what I noticed was that I needed to select unlooked (in
format control) so that the button would be copied over to the new sheet.

Is there any way of leaving the button locked so that it can not be deleted
yet still be available on the new sheet?

Regards
Jorge

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

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



:

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

:

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

:

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 Before:=Sheets(1)

'Following line adds sheet before active sheet
'Sheets.Add Before:=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

:

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

:

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 it’s 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.
That’s 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

:

Thanks for the kudos. It’s 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?
What is sheet name and cell reference to which it is linked?
Can you create the link manually? If so, then create it and then select the
cell and then on the formula bar at the top, highlight the formula and then
copy it and then paste it into a posting here.

Regards,

OssieMac


:

Thanks OssieMac,

It worked brilliently, very impressive indeed.

One more thing though, one particular cell I would like to be paste linked.
Is that possible?


:

I didn’t wait for your answer to my question. However, see what this does for
you.
The following code adds a worksheet to an active workbook and copies the
active work sheet to the new work sheet.
Because you said that you have limited experience with Visual Basic, I will
step through what you need to do.
Ensure that you have a backup copy of your workbook in case the macro does
not do exactly what you want.
The macro is designed to be run from the active sheet in the workbook and
this is the one it copies to the new sheet.

Shift/F11 will open the VBA Editor.
Select the menu item Insert->Module.
Copy the code into the module.

A single quote is used to mark text as a remark and the text should be
green. (This text is ignored by the program.)
If there are any lines in red then it is probably because the lines ended up
with a line feed between when I posted the code and you copied it into the
VBA editor. Use the delete key at the end of the first red line in the group
and bring the following red lines up into one line and when you move off the
line it should be black (with a bit of blue).

You will see some code with comments that gives you options on where you
want the new worksheet to be placed. As I have posted the code, the only
active one is the first one which places the worksheet before the first
existing worksheet. Only one of these lines is to be active but you can
change where you want the worksheet by placing a single quote in front of the
existing active one and remove the single quote from one of the others.

Close the VBA Editor and then save the workbook. If you are working in
xl2007 then save as a macro enabled workbook.

To run the macro select from the workbook menu items. If the macro does what
you want, then I will think about a toolbar button for you to run it but let
us get it doing what you want first.

All users will need to have options set to ‘macro enabled’. Look up help on
this if you are unsure what to do here.

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"

Do
wShtExists = False
Beep
newShtName = InputBox(prompt:=inputPrompt, _
Title:="New Sheet Name")
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 Before:=Sheets(1)

 

[Guest]

Hi Again Jorge,

This has become war and peace.

Protecting the new sheet.
Before you do anything with the following code, I advise making a backup of
your workbook in case of problems.

Your question about identifying and matching the locked and unlocked cells
in the new sheet intrigued me enough to research it. Below is yet another
version of code for you. You still have to insert your code into it and I
have put a comment in uppercase where I think it should go.

You also have to edit the range A1:K40 to match the range that you are using
on your spreadsheets. Don’t try to use the entire spreadsheet range or you
will be waiting until the New Year for the procedure to run. Simply include a
range large enough to cover all the data on your sheet. Doesn’t matter if it
is larger but can’t be smaller.

I am assuming that you know that protecting the sheet is a 2 step procedure.
Firstly step is to unlock cells that the user will be allowed to edit and
second step is to protect the sheet using a password.

The code identifies the unlocked cells on the original sheet and unlocks the
corresponding cells on the new sheet. It then protects the new sheet with a
password and then re-protects the original sheet with the password.

My thoughts are that the old sheet should be totally protected to prevent
users making retro changes to it. If you would like to be able to do this, I
have included a line of code that you can uncomment to do it. However read
and heed the comment in upper case because once you run it with this line,
all cells on the original will be locked and you will not be able to use it
as a sample to create a new sheet with the specific unlocked cells. You will
need to manually unlock the editing cells again if you still need to make
further adjustments to the code etc to get everything right. Note that the
USER WITH THE PASSWORD will still be able to make changes to the old sheet if
required and it doesn’t matter whether the cells are locked or unlocked once
it is unprotected with the password.

I am going away for a week to ten days from Tuesday so if you need any final
tweaking then I need to know about it by early Monday or it waits until I get
home again.

Sub Insert_New_Sheet()

Dim oldShtName As String
Dim newShtName As String
Dim wSht As Object
Dim cellAddress As String
Dim workRnge As Range
Dim c1 As Range

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

'Unprotect so that button will copy
'Replace OssieMac with your password.
Sheets(oldShtName).Unprotect ("OssieMac")

'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 Before:=Sheets(1)

'Following line adds sheet before active sheet
'Sheets.Add Before:=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

'INSERT YOUR CODE HERE.

'All the following code should be last code in
'procedure.
'Unlock cells on new sheet to match the unlocked
'cells in the original sheet. (ie. Cells that
'the user is allowed to edit.

'Edit code "A1:K40" in next line to match the
'working range on YOUR original sheet.
Set workRnge = Sheets(oldShtName).Range("A1:K40")
For Each c1 In workRnge
If c1.Locked = False Then
cellAddress = c1.Address
Sheets(newShtName).Range(cellAddress) _
.Locked = False
End If
Next c1

'Protect the new sheet
'Replace OssieMac with your password
Sheets(newShtName).Protect Password:="OssieMac", _
DrawingObjects:=True, _
Contents:=True, _
Scenarios:=True

'Reprotect the original sheet.
'Replace OssieMac with your password.

'Uncomment the following line of code if you
'want to prevent retro changes to the old
'sheet by anyone without the password.
'DO NOT UNCOMMENT THIS LINE UNTIL YOU HAVE
'TOTALLY FINISHED TESTING THE PROCEDURE AND
'EVEN THEN, MAKE SURE THAT YOU HAVE A BACKUP
'OF THE WORKBOOK.

'Sheets(oldShtName).Cells.Locked = True

Sheets(oldShtName).Protect Password:="OssieMac", _
DrawingObjects:=True, _
Contents:=True, _
Scenarios:=True

End Sub

Regards,

OssieMac

The only way that I know of copying the button is to unprotect the sheet,
copy it and then protect it again. Another version of the code below to show
you how to do it.

With the code you posted for protecting all worksheets I think that it still
requires you to unlock the cells that users are allowed to alter before
applying the protection. This will also apply to the newly created worksheet
and your best way is to record the unlocking. You don't have to do this to
the original again just because you unprotect it because unprotection does
not remove the unlocked info.

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

oldShtName = ActiveSheet.Name

'Unprotect so that button will copy
'Replace OssieMac with your password.
Sheets(oldShtName).Unprotect ("OssieMac")

'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 Before:=Sheets(1)

'Following line adds sheet before active sheet
'Sheets.Add Before:=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

'Reprotect the original sheet
'Replace OssieMac with your password
Sheets(oldShtName).Protect Password:="OssieMac", _
DrawingObjects:=True, _
Contents:=True, _
Scenarios:=True


End Sub

Regards,

OssieMac

Hi OssieMac,

I created a button to assign the macro as away of passing it on to other
users. However, what I noticed was that I needed to select unlooked (in
format control) so that the button would be copied over to the new sheet.

Is there any way of leaving the button locked so that it can not be deleted
yet still be available on the new sheet?

Regards
Jorge

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


:

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



:

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

:

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

:

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 Before:=Sheets(1)

'Following line adds sheet before active sheet
'Sheets.Add Before:=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

:

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

:

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 it’s 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.
That’s 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

:

Thanks for the kudos. It’s 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

 

[Guest]

Hi OssieMac,

The system would allow me to reply to your last to responses so I that's why
I'm replying from here.

Your second last code has been doing everything beatifully once I replaced
the last code to protect the worksheets.
'Sheets(oldShtName).Cells.Locked = True
Sheets(oldShtName).Protect Password:="OssieMac", _
DrawingObjects:=True, _
Contents:=True, _
Scenarios:=True

With:

For Each ws In ActiveWorkbook.Worksheets
If ws.ProtectContents = False Then
ws.Protect ("1")
End If
Next
Application.ScreenUpdating = True

It appeared that "your" way was leaving the new sheet unprotected.

I also tried the last code you sent and encountered a Run-time error '1004':
Unable to set the locked property of the Range Class.

To be honest I'm not totally sure what the last code was meant to do
differently but in any case it appears that you have provided me with exactly
what I was after.

Cheers OssieMac. I may never be able to repay you for all your efforts but I
pray you get yours back 100 fold.

Regards
Jorge

Hi Again Jorge,

In answer to your question, I’m 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 Before:=Sheets(1)

'Following line adds sheet before active sheet
'Sheets.Add Before:=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

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

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



:

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

:

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

:

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 Before:=Sheets(1)

'Following line adds sheet before active sheet
'Sheets.Add Before:=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

:

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

:

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 it’s 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.
That’s 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

:

Thanks for the kudos. It’s 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?