Hi,

I am trying to create a macro to insert a header into my worksheets. I
would like to be able to enter what the header is to say in a cell and create
a macro to copy those contents into the header portion to save me some time.
The catch is that the titles that i need to type in will change from time to
time, so the macro must be able to copy and paste dynamically. For example
this month the sheet might say "Global Economic Outlook" but next quarter it
might say, "Affects of Oil Demand". How can I get a macro to enter the
different data from the contents of a cell?

Thanks,

Dan

Hi,

We write "Global Economic Outlook" to a cell in your worksheet (A1?) how
does this tell us where to get the data from and where to put it?

Mike

"Stonewall" wrote:

Hi,

I am trying to create a macro to insert a header into my worksheets. I
would like to be able to enter what the header is to say in a cell and create
a macro to copy those contents into the header portion to save me some time.
The catch is that the titles that i need to type in will change from time to
time, so the macro must be able to copy and paste dynamically. For example
this month the sheet might say "Global Economic Outlook" but next quarter it
might say, "Affects of Oil Demand". How can I get a macro to enter the
different data from the contents of a cell?

Thanks,

Dan

Instead of a cell you could use

myheader=inputbox("Enter this months header")
range("a1").value=myheader

or a simple formula using your cell idea
=sheet1!c1


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Stonewall" wrote in message
...
Hi,

I am trying to create a macro to insert a header into my worksheets. I
would like to be able to enter what the header is to say in a cell and
create
a macro to copy those contents into the header portion to save me some
time.
The catch is that the titles that i need to type in will change from time
to
time, so the macro must be able to copy and paste dynamically. For
example
this month the sheet might say "Global Economic Outlook" but next quarter
it
might say, "Affects of Oil Demand". How can I get a macro to enter the
different data from the contents of a cell?

Thanks,

Dan

This is the code that I have so far:

Dim cn As String
cn = InputBox("Please Enter Slide Title", "Slide Title")

If cn < "" Then
Range("S2").Value = cn
End If

Range("S2").Select
ActiveCell.Copy
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = _
"&""Times New Roman,Regular""&24"Global Economic Outlook""

Is there any way i can get "Global Economic Outlook" simply to be the
contents of cell S2? Will the code that you wrote down do the trick?

Thanks.

"Don Guillett" wrote:

Instead of a cell you could use

myheader=inputbox("Enter this months header")
range("a1").value=myheader

or a simple formula using your cell idea
=sheet1!c1


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Stonewall" wrote in message
...
Hi,

I am trying to create a macro to insert a header into my worksheets. I
would like to be able to enter what the header is to say in a cell and
create
a macro to copy those contents into the header portion to save me some
time.
The catch is that the titles that i need to type in will change from time
to
time, so the macro must be able to copy and paste dynamically. For
example
this month the sheet might say "Global Economic Outlook" but next quarter
it
might say, "Affects of Oil Demand". How can I get a macro to enter the
different data from the contents of a cell?

Thanks,

Dan

Never do more to page setup than necessary as it is SLOOOOW

Sub setheaderfromrange()
With ActiveSheet.PageSetup
.LeftHeader = "&""Times New Roman,Regular""&24" & Range("s2")
End With
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Stonewall" wrote in message
...
This is the code that I have so far:

Dim cn As String
cn = InputBox("Please Enter Slide Title", "Slide Title")

If cn < "" Then
Range("S2").Value = cn
End If

Range("S2").Select
ActiveCell.Copy
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = _
"&""Times New Roman,Regular""&24"Global Economic Outlook""

Is there any way i can get "Global Economic Outlook" simply to be the
contents of cell S2? Will the code that you wrote down do the trick?

Thanks.

"Don Guillett" wrote:

Instead of a cell you could use

myheader=inputbox("Enter this months header")
range("a1").value=myheader

or a simple formula using your cell idea
=sheet1!c1


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Stonewall" wrote in message
...
Hi,

I am trying to create a macro to insert a header into my worksheets. I
would like to be able to enter what the header is to say in a cell and
create
a macro to copy those contents into the header portion to save me some
time.
The catch is that the titles that i need to type in will change from
time
to
time, so the macro must be able to copy and paste dynamically. For
example
this month the sheet might say "Global Economic Outlook" but next
quarter
it
might say, "Affects of Oil Demand". How can I get a macro to enter the
different data from the contents of a cell?

Thanks,

Dan

Thank you! That worked perfectly.

"Don Guillett" wrote:

Never do more to page setup than necessary as it is SLOOOOW

Sub setheaderfromrange()
With ActiveSheet.PageSetup
.LeftHeader = "&""Times New Roman,Regular""&24" & Range("s2")
End With
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Stonewall" wrote in message
...
This is the code that I have so far:

Dim cn As String
cn = InputBox("Please Enter Slide Title", "Slide Title")

If cn < "" Then
Range("S2").Value = cn
End If

Range("S2").Select
ActiveCell.Copy
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = _
"&""Times New Roman,Regular""&24"Global Economic Outlook""

Is there any way i can get "Global Economic Outlook" simply to be the
contents of cell S2? Will the code that you wrote down do the trick?

Thanks.

"Don Guillett" wrote:

Instead of a cell you could use

myheader=inputbox("Enter this months header")
range("a1").value=myheader

or a simple formula using your cell idea
=sheet1!c1


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Stonewall" wrote in message
...
Hi,

I am trying to create a macro to insert a header into my worksheets. I
would like to be able to enter what the header is to say in a cell and
create
a macro to copy those contents into the header portion to save me some
time.
The catch is that the titles that i need to type in will change from
time
to
time, so the macro must be able to copy and paste dynamically. For
example
this month the sheet might say "Global Economic Outlook" but next
quarter
it
might say, "Affects of Oil Demand". How can I get a macro to enter the
different data from the contents of a cell?

Thanks,

Dan

Glad to help.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Stonewall" wrote in message
...
Thank you! That worked perfectly.

"Don Guillett" wrote:

Never do more to page setup than necessary as it is SLOOOOW

Sub setheaderfromrange()
With ActiveSheet.PageSetup
.LeftHeader = "&""Times New Roman,Regular""&24" & Range("s2")
End With
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Stonewall" wrote in message
...
This is the code that I have so far:

Dim cn As String
cn = InputBox("Please Enter Slide Title", "Slide Title")

If cn < "" Then
Range("S2").Value = cn
End If

Range("S2").Select
ActiveCell.Copy
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = _
"&""Times New Roman,Regular""&24"Global Economic Outlook""

Is there any way i can get "Global Economic Outlook" simply to be the
contents of cell S2? Will the code that you wrote down do the trick?

Thanks.

"Don Guillett" wrote:

Instead of a cell you could use

myheader=inputbox("Enter this months header")
range("a1").value=myheader

or a simple formula using your cell idea
=sheet1!c1


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Stonewall" wrote in message
...
Hi,

I am trying to create a macro to insert a header into my worksheets.
I
would like to be able to enter what the header is to say in a cell
and
create
a macro to copy those contents into the header portion to save me
some
time.
The catch is that the titles that i need to type in will change from
time
to
time, so the macro must be able to copy and paste dynamically. For
example
this month the sheet might say "Global Economic Outlook" but next
quarter
it
might say, "Affects of Oil Demand". How can I get a macro to enter
the
different data from the contents of a cell?

Thanks,

Dan

I have run into another problem with setting the footer using this method as
well. I want to put page numbers on the footer with using the reference
"s3". When i run the code (adapted, of course) it will work fine with text,
but not with numbers. I even formatted the numbers as text and it did not
work.

Also I was wondering if there was a way to set all the headers and footers
by this method in a workbook at once, rather than having to go to each sheet
and run the macro.

Thanks in advance.

"Don Guillett" wrote:

Glad to help.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Stonewall" wrote in message
...
Thank you! That worked perfectly.

"Don Guillett" wrote:

Never do more to page setup than necessary as it is SLOOOOW

Sub setheaderfromrange()
With ActiveSheet.PageSetup
.LeftHeader = "&""Times New Roman,Regular""&24" & Range("s2")
End With
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Stonewall" wrote in message
...
This is the code that I have so far:

Dim cn As String
cn = InputBox("Please Enter Slide Title", "Slide Title")

If cn < "" Then
Range("S2").Value = cn
End If

Range("S2").Select
ActiveCell.Copy
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = _
"&""Times New Roman,Regular""&24"Global Economic Outlook""

Is there any way i can get "Global Economic Outlook" simply to be the
contents of cell S2? Will the code that you wrote down do the trick?

Thanks.

"Don Guillett" wrote:

Instead of a cell you could use

myheader=inputbox("Enter this months header")
range("a1").value=myheader

or a simple formula using your cell idea
=sheet1!c1


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Stonewall" wrote in message
...
Hi,

I am trying to create a macro to insert a header into my worksheets.
I
would like to be able to enter what the header is to say in a cell
and
create
a macro to copy those contents into the header portion to save me
some
time.
The catch is that the titles that i need to type in will change from
time
to
time, so the macro must be able to copy and paste dynamically. For
example
this month the sheet might say "Global Economic Outlook" but next
quarter
it
might say, "Affects of Oil Demand". How can I get a macro to enter
the
different data from the contents of a cell?

Thanks,

Dan