Tab, Header, Footer macro help sought (Resolved)

Jak

Well-known Member
Joined
Apr 5, 2002
Messages
833
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
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
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
 
Upvote 0
Hi Jak

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

Tom

<pre>


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


</pre>
 
Upvote 0
Thanks to everyone for their input.

:0)
This message was edited by Jak on 2002-04-30 10:35
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,194
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top