Tab, Header, Footer macro help sought (Resolved)

[Jak]

I need assistance with a macro.

I have a macro that copies the active worksheet, opens a new worksheet and paste the copied sheet.

What I also need the macro to do is assign todays date in the left hand portion of the footer. Assign the time to the right hand portion of the footer.

I then need a user promp that takes a mixture of letters and numbers and assigns the digets to the new sheets tab and to the sheets header.

Any ideas or code would be appreciated.

[ This Message was edited by: Jak on 2002-05-15 06:26 ]

[Juan Pablo González]

Your first question (Your topic's title, BTW):

Sheet1.PageSetup.LeftFooter = Date

Second one... not clear for me.

[Jak]

Hi Juan

Thanks for the first bit of code.

I don't think I worded my question very well. Basically, after I get a new worksheet I have to re-name the tab. eg.sheet2 may be renamed as PH3456. The didgets are never sequential hence I need a way of prompting the user to enter i.e PH3456. As this text will also be used as the sheets header I need a way to re-name the sheets tab and header at the same time. If you have any further thoughts or questions let me know. Thanks for your initial help.

[ This Message was edited by: Jak on 2002-04-27 10:59 ]

[Tom Schreiner]

Hi Jak

Call this macro at the end of your current macro...

Tom

Sub GetSheetName()

Dim NewSheetName

Dim MessageBoxMsg As String

On Error Resume Next

ReEnter:

NewSheetName = InputBox("Please enter the name for this sheet")

If Len(NewSheetName) = 0 Then

    MessageBoxMsg = MsgBox("Do you wish to use the current " & _

                           "sheet name: " & ActiveSheet.Name & " ? ", _

                           vbYesNo, "Error")

    If MessageBoxMsg = vbNo Then GoTo ReEnter

End If

Err.Clear

ActiveSheet.Name = NewSheetName

If Err.Number <> 0 Then

    MsgBox "Invalid Sheet Name - Please choose another name"

    GoTo ReEnter

End If

With ActiveSheet.PageSetup

    .LeftHeader = ""

    .CenterHeader = "&A"

    .RightHeader = ""

    .LeftFooter = "&D"

    .CenterFooter = ""

    .RightFooter = "&T"

End With

End Sub

[Jak]

TsTom

Thanks for the code it works great.

[Jak]

Thanks to everyone for their input.

:0)

[ This Message was edited by: Jak on 2002-04-30 10:35 ]