Copy Contents from Cell

[Stonewall]

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

 

[Mike H]

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

 

[Don Guillett]

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

 

[Stonewall]

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]

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

 

[Stonewall]

Thank you! That worked perfectly.

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 address removed)

 

[Don Guillett]

Glad to help.

 

[Stonewall]

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.