Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 82
Default Copy Column data to Page Header

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.
--
By persisting in your path, though you forfeit the little, you gain the
great.

  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,355
Default Copy Column data to Page Header

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

http://www.mvps.org/dmcritchie/excel/pathname.htm
--
HTH,
Barb Reinhardt



"DavidH56" wrote:

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.
--
By persisting in your path, though you forfeit the little, you gain the
great.

  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 82
Default Copy Column data to Page Header

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.
--
By persisting in your path, though you forfeit the little, you gain the
great.



"Barb Reinhardt" wrote:

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

http://www.mvps.org/dmcritchie/excel/pathname.htm
--
HTH,
Barb Reinhardt



"DavidH56" wrote:

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.
--
By persisting in your path, though you forfeit the little, you gain the
great.

  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 82
Default Copy Column data to Page Header

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


--
By persisting in your path, though you forfeit the little, you gain the
great.



"Barb Reinhardt" wrote:

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

http://www.mvps.org/dmcritchie/excel/pathname.htm
--
HTH,
Barb Reinhardt



"DavidH56" wrote:

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.
--
By persisting in your path, though you forfeit the little, you gain the
great.

  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,355
Default Copy Column data to Page Header

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

--
HTH,
Barb Reinhardt



"DavidH56" wrote:

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


--
By persisting in your path, though you forfeit the little, you gain the
great.



"Barb Reinhardt" wrote:

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

http://www.mvps.org/dmcritchie/excel/pathname.htm
--
HTH,
Barb Reinhardt



"DavidH56" wrote:

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.
--
By persisting in your path, though you forfeit the little, you gain the
great.



  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 82
Default Copy Column data to Page Header

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

Thanks
--
By persisting in your path, though you forfeit the little, you gain the
great.



"Barb Reinhardt" wrote:

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

--
HTH,
Barb Reinhardt



"DavidH56" wrote:

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


--
By persisting in your path, though you forfeit the little, you gain the
great.



"Barb Reinhardt" wrote:

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

http://www.mvps.org/dmcritchie/excel/pathname.htm
--
HTH,
Barb Reinhardt



"DavidH56" wrote:

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.
--
By persisting in your path, though you forfeit the little, you gain the
great.

  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,355
Default Copy Column data to Page Header

It appears that I forgot to dimension Sentences and define it at the
beginning of the sub. :(
--
HTH,
Barb Reinhardt



"DavidH56" wrote:

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

Thanks
--
By persisting in your path, though you forfeit the little, you gain the
great.



"Barb Reinhardt" wrote:

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

--
HTH,
Barb Reinhardt



"DavidH56" wrote:

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


--
By persisting in your path, though you forfeit the little, you gain the
great.



"Barb Reinhardt" wrote:

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

http://www.mvps.org/dmcritchie/excel/pathname.htm
--
HTH,
Barb Reinhardt



"DavidH56" wrote:

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.
--
By persisting in your path, though you forfeit the little, you gain the
great.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Grouped sheets page formatting will copy the header James Trujillo Excel Worksheet Functions 3 December 9th 09 01:26 AM
Copy data from other worksheet based on column header [email protected] Excel Discussion (Misc queries) 0 April 29th 08 08:18 PM
How can I copy the header and footers onto every page on a workboo Lesley Shand Excel Worksheet Functions 1 September 1st 07 03:34 PM
Search for a column based on the column header and then past data from it to another column in another workbook minkokiss Excel Programming 2 April 5th 07 01:12 AM
How do I print the 1st column as a row header in each page? Lighthouse1 New Users to Excel 9 September 7th 06 04:28 AM


All times are GMT +1. The time now is 06:06 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"