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
(E-Mail Removed)
"Stonewall" <(E-Mail Removed)> wrote in message
news:50ED7849-15F5-4051-84CB-(E-Mail Removed)...
> 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
> (E-Mail Removed)
> "Stonewall" <(E-Mail Removed)> wrote in message
> news:50ED7849-15F5-4051-84CB-(E-Mail Removed)...
> > 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
(E-Mail Removed)
"Stonewall" <(E-Mail Removed)> wrote in message
news:42BC70EF-949E-4F50-98A8-(E-Mail Removed)...
> 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
>> (E-Mail Removed)
>> "Stonewall" <(E-Mail Removed)> wrote in message
>> news:50ED7849-15F5-4051-84CB-(E-Mail Removed)...
>> > 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
> (E-Mail Removed)
> "Stonewall" <(E-Mail Removed)> wrote in message
> news:42BC70EF-949E-4F50-98A8-(E-Mail Removed)...
> > 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
> >> (E-Mail Removed)
> >> "Stonewall" <(E-Mail Removed)> wrote in message
> >> news:50ED7849-15F5-4051-84CB-(E-Mail Removed)...
> >> > 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
(E-Mail Removed)
"Stonewall" <(E-Mail Removed)> wrote in message
news:8C5329C3-666A-4261-A84A-(E-Mail Removed)...
> 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
>> (E-Mail Removed)
>> "Stonewall" <(E-Mail Removed)> wrote in message
>> news:42BC70EF-949E-4F50-98A8-(E-Mail Removed)...
>> > 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
>> >> (E-Mail Removed)
>> >> "Stonewall" <(E-Mail Removed)> wrote in message
>> >> news:50ED7849-15F5-4051-84CB-(E-Mail Removed)...
>> >> > 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
> (E-Mail Removed)
> "Stonewall" <(E-Mail Removed)> wrote in message
> news:8C5329C3-666A-4261-A84A-(E-Mail Removed)...
> > 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
> >> (E-Mail Removed)
> >> "Stonewall" <(E-Mail Removed)> wrote in message
> >> news:42BC70EF-949E-4F50-98A8-(E-Mail Removed)...
> >> > 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
> >> >> (E-Mail Removed)
> >> >> "Stonewall" <(E-Mail Removed)> wrote in message
> >> >> news:50ED7849-15F5-4051-84CB-(E-Mail Removed)...
> >> >> > 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
> >> >>
> >> >>
> >>
> >>
>
>