Copy Column data to Page Header

[DavidH56]

Hi,
I would like assistance to write a macro which creates a page center to read
from column data. I would like for example the text string to read "Wasuto
updates for Supervisor ?? data date ??. Wasuto would be placed if column 4
has "WAS" in the 12 character text (example:45WAS51302U35) or if "KEM" is
there instead I'd like the word "Kematsa" instead. Also I'd like the
Supervisor's name if column 5 lists his name greater than 50% of the time.
There could be up to 25 different Supervisors names. Lastly I'd like the
current date behind the word date in the string. I'm using excel 2003. There
could be up to 5000 rows of data. The firts row is the header row.
Any help would be greatly appreciated.

 

[Barb Reinhardt]

Take a look here to find out how to use the Workbook_BeforePrint event.

http://www.mvps.org/dmcritchie/excel/pathname.htm

 

[DavidH56]

Thanks for your response Barb

There is a lot of good info I get here. I can see how to add the file path
and insert the date in my page header. I still would like to know which code
to use, maybe select case to insert the first word "Wasuto" in the page
center header if column 4 has "WAS" in it. Also pulling Supervisor's names
from column 5 to insert in page header also. Thanks again for your help.

 

[DavidH56]

Thanks Barb,

This is what I have so far:

Sub FixUpdateHeader()
Dim Sentences
Dim i As Long
Dim iWordPos As Integer

Sentences = Range("D1", Range("D65536").End(xlUp))
lRow = 0
For i = Range("D65536").End(xlUp).Row To 1 Step -1
iWordPos = InStr(LCase(Sentences(i, 1)), LCase("WAS"))
If iWordPos > 0 Then
With ActiveSheet
.PageSetup.CenterHeader = "Wasuto Updates"
End With
End If
Next i
For i = Range("D65536").End(xlUp).Row To 1 Step -1
iWordPos = InStr(LCase(Sentences(i, 1)), LCase("KRS"))
If iWordPos > 0 Then
With ActiveSheet
.PageSetup.CenterHeader = "Kematsa Updates"
End With
End If
Next i
End Sub

 

[Barb Reinhardt]

I think I'd replace some of the For i = ... next i with this

Call DefineHeader("KRS","Kematsa Updates")

Sub DefineHeader(myAcronym as string, myUpdates as string)
For i = Range("D65536").End(xlUp).Row To 1 Step -1
iWordPos = InStr(LCase(Sentences(i, 1)), LCase(myacronym))
If iWordPos > 0 Then
With ActiveSheet
.PageSetup.CenterHeader = myupdates
End With
End If
Next i
end sub

 

[DavidH56]

Barb,
I get a type mismatch on this line of code.
iWordPos = InStr(LCase(Sentences(i, 1)), LCase(myAcronym))

Thanks

 

[Barb Reinhardt]

It appears that I forgot to dimension Sentences and define it at the
beginning of the sub.

 

[DavidH56]

Thanks again Barb,

I've got it now.