Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Grouped sheets page formatting will copy the header | Excel Worksheet Functions | |||
Copy data from other worksheet based on column header | Excel Discussion (Misc queries) | |||
How can I copy the header and footers onto every page on a workboo | Excel Worksheet Functions | |||
Search for a column based on the column header and then past data from it to another column in another workbook | Excel Programming | |||
How do I print the 1st column as a row header in each page? | New Users to Excel |