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

Regards
Jorge

"OssieMac" wrote:

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

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

oldShtName = ActiveSheet.Name

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

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

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

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

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

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

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

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

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

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

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

End Sub

Regards,

OssieMac

"JorgeG.ACT" wrote:

Sorry OssieMac,

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

Regards
Jorgeg.ACT

"JorgeG.ACT" wrote:

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

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

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

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

Regards
Jorgeg.ACT

"OssieMac" wrote:

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


"JorgeG.ACT" wrote:

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?


"OssieMac" wrote:

I didnt 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 Befo=Sheets(1)

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

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

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

ActiveSheet.Name = newShtName
Sheets(oldShtName).Cells.Copy
Sheets(newShtName).Paste
Application.CutCopyMode = False
Range("A1").Select

End Sub

Regards,

OssieMac

"OssieMac" wrote:

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

"JorgeG.ACT" wrote:

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.