adding work sheets

[John D. Inkster]

When I add a work sheet to a work book , Is there a way to add the header &
footer settings to the new work sheet?

Thanks
John Inkster

 

[Earl Kiosterud]

John,

If the worksheet will be similar to existing worksheets, consider copying an
existing sheet, then removing the data and whatever else is necessary from
it. The new sheet will have all the stuff the original did.

 

[John D. Inkster]

Thanks Earl

That was sweet. When I tried it my way to include all the page formatting
it took a good 20 seconds to format the page, and the screen was blinking
like it had sand behind it's eye lid <G>.

When I tried to incorporate a work sheet name change (it worked as a stand
alone macro) I end up with two work sheets, a copy of the original work
sheet ("blank time card (2)) and a second work sheet with the name from cell
"f4" but it is totally blank......

Sub copypage()
'
' copypage Macro
' Macro recorded 1/21/2007 by Inkster
' On Error Resume Next
If Range("t4").Value > 0 _
And Range("u4").Value > 0 _
And Range("v4").Value > 0 _
And Range("f4").Value > " " _
Then
' this part copies the work sheet
Sheets("Blank Time Card").Select
Sheets("Blank Time Card").Copy Before:=Sheets(2)

MsgBox "Time Card Saved", vbOKOnly + vbExclamation
Else
MsgBox "Date Not Correct, Or Name Not Entered. Time Sheet Not
Saved", _
vbOKOnly + vbExclamation
End If
' this part won't rename the worksheet
With Worksheets.Add.Name = Sheets("Blank Time Card(2)").Range("f4").Value
End With
End Sub

any thoughts?

Thanks
John

 

[Earl Kiosterud]

John,

I don't think I understand why you're doing both of these things:

1.

Sheets("Blank Time Card").Select
Sheets("Blank Time Card").Copy Before:=Sheets(2)

which would give you a copy of sheet
"Blank Time Card" with the name "Blank Time Card (2)"

2.
Worksheets.Add.Name = Sheets("Blank Time Card(2)").Range("f4").Value

which will add a sheet named from cell F4.

By the way, the first one can be reduced to
Sheets("Blank Time Card").Copy Before:=Sheets(2)
though the code may run differently, as the same sheet won't necessarily be
the selected one as with before.

The second can be reduced to
Worksheets.Add.Name = Sheets("Blank Time Card(2)").Range("f4").Value
dropping the With stuff, since you're not really using it.

Try stepping through your code with F8, Alt-Tabbing back to Excel to see
what it's doing.

 

[John D. Inkster]

Hi Earl

What I am trying to do is make a copy of worksheet"Blank Time Card"
and name it with the value of Range("f4") and include all the data on
the work sheet.

One way all I get is a blank sheet with the name from cell "f4".

The other way I get all the data from "Blank Time Card", but the
macro buttons are all messed up and the page formating is nowhere even
close to the original sheet????

So now I'm just guessing!

And this is only the start

John

 

[Pete_UK]

try this:


Sub copy_sheet()
Dim my_sheet As String
my_sheet = Range("F4").Value
ActiveSheet.Copy After:=Sheets(1)
ActiveSheet.name = my_sheet
End Sub


It will copy the sheet that you are on when you invoke the macro and
give the copied sheet the name given in F4.

Hope this helps

 

[John D. Inkster]

Hi Earl

I got it with this.....

Sub copy_sheet()
Dim my_sheet As String
my_sheet = Range("D9").Value
ActiveSheet.Copy After:=Sheets(1)
ActiveSheet.name = my_sheet
End Sub

Thanks to Pete_UK, his responce to Little_Kitty

 

[John D. Inkster]

I tried it just before you posted this reply, work great.

Thanks
John Inkster

 

[Pete_UK]

Thanks for the feedback, John - glad it worked. You might like to amend
it slightly if there is any chance that F4 is empty:

Sub copy_sheet()
Dim my_sheet As String
my_sheet = Range("F4").Value
If my_sheet = "" Then my_sheet = "no_name"
ActiveSheet.Copy After:=Sheets(1)
ActiveSheet.name = my_sheet
End Sub

This will give the sheet the name "no_name" if you do not have anything
in F4.

Hope this helps further.

Pete