Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I change Headers and footers in selected Reports at the same .
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I change Headers and footers in selected Reports at the same .
maybe...
If you're writing about worksheets in the same workbook, you can group the sheets (click on the first tab and ctrl-click on subsequent tabs). Then File|page setup and change the headers/footers. Remember to ungroup the sheets (just rightclick on any of the grouped sheets and select ungroup sheets) or you may damage your workbook. Almost anything you do to a one sheet (of a group) is done to the rest of the sheets in that group. Tester wrote: -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I change Headers and footers in selected Reports at the sa
Thanks Dave, but no, I need to change headers and footers in over a hundred
different folders on a regular basis. As the headers/footers are all the same, I need to be able to select all the folders I want to change, and change as a batch. "Dave Peterson" wrote: maybe... If you're writing about worksheets in the same workbook, you can group the sheets (click on the first tab and ctrl-click on subsequent tabs). Then File|page setup and change the headers/footers. Remember to ungroup the sheets (just rightclick on any of the grouped sheets and select ungroup sheets) or you may damage your workbook. Almost anything you do to a one sheet (of a group) is done to the rest of the sheets in that group. Tester wrote: -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I change Headers and footers in selected Reports at the sa
You could record a macro when you change one of the headers/footers. Tweak it
to limit it to only the stuff you need to do (delete the stuff that gets recorded that you don't want to change). Then you could modify that code to look at the worksheets in the workbooks that you want updated. Kind of like this: Option Explicit Sub testme() Dim myFileNames As Variant Dim iCtr As Long Dim wkbk As Workbook Dim wks As Worksheet myFileNames = Application.GetOpenFilename _ ("Excel Files,*.xls", MultiSelect:=True) If IsArray(myFileNames) = False Then Exit Sub 'user hit cancel End If For iCtr = LBound(myFileNames) To UBound(myFileNames) Set wkbk = Workbooks.Open(Filename:=myFileNames(iCtr)) For Each wks In wkbk.Worksheets 'fix that header/footer (your modified code here) Next wks wkbk.Close savechanges:=True Next iCtr End Sub The multiselect:=true means that you can select multiple files (click on the first, ctrl-click on subsequent) in the File|Open dialog. If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Tester wrote: Thanks Dave, but no, I need to change headers and footers in over a hundred different folders on a regular basis. As the headers/footers are all the same, I need to be able to select all the folders I want to change, and change as a batch. "Dave Peterson" wrote: maybe... If you're writing about worksheets in the same workbook, you can group the sheets (click on the first tab and ctrl-click on subsequent tabs). Then File|page setup and change the headers/footers. Remember to ungroup the sheets (just rightclick on any of the grouped sheets and select ungroup sheets) or you may damage your workbook. Almost anything you do to a one sheet (of a group) is done to the rest of the sheets in that group. Tester wrote: -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I change Headers and footers in selected Reports at the sa
Thanks for the help Dave. I am afraid that I have had very little experience
with Visual Basic, but I finaly made your program work with my recorded macro. This has saved me a great deal of work. I am still having a problem though, as the Header/Footer is restricted to the first sheet in the workbook only. The program appears to select all sheets, but the Headers are not there. I have also tried recording a macro selecting all sheets as in your first reply. This works when done individualy, but not when applying the macro. The sheets are selected as in your program, but again the Header is not applied. Any sugestions? "Dave Peterson" wrote: You could record a macro when you change one of the headers/footers. Tweak it to limit it to only the stuff you need to do (delete the stuff that gets recorded that you don't want to change). Then you could modify that code to look at the worksheets in the workbooks that you want updated. Kind of like this: Option Explicit Sub testme() Dim myFileNames As Variant Dim iCtr As Long Dim wkbk As Workbook Dim wks As Worksheet myFileNames = Application.GetOpenFilename _ ("Excel Files,*.xls", MultiSelect:=True) If IsArray(myFileNames) = False Then Exit Sub 'user hit cancel End If For iCtr = LBound(myFileNames) To UBound(myFileNames) Set wkbk = Workbooks.Open(Filename:=myFileNames(iCtr)) For Each wks In wkbk.Worksheets 'fix that header/footer (your modified code here) Next wks wkbk.Close savechanges:=True Next iCtr End Sub The multiselect:=true means that you can select multiple files (click on the first, ctrl-click on subsequent) in the File|Open dialog. If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Tester wrote: Thanks Dave, but no, I need to change headers and footers in over a hundred different folders on a regular basis. As the headers/footers are all the same, I need to be able to select all the folders I want to change, and change as a batch. "Dave Peterson" wrote: maybe... If you're writing about worksheets in the same workbook, you can group the sheets (click on the first tab and ctrl-click on subsequent tabs). Then File|page setup and change the headers/footers. Remember to ungroup the sheets (just rightclick on any of the grouped sheets and select ungroup sheets) or you may damage your workbook. Almost anything you do to a one sheet (of a group) is done to the rest of the sheets in that group. Tester wrote: -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I change Headers and footers in selected Reports at the sa
This portion loops through all the worksheets in the workbook:
For Each wks In wkbk.Worksheets 'fix that header/footer (your modified code here) Next wks If you don't need that portion, just write your code to fix the single sheet: wkbk.worksheets("sheet1")... or if it's always the leftmost: wkbk.worksheets(1).... Tester wrote: Thanks for the help Dave. I am afraid that I have had very little experience with Visual Basic, but I finaly made your program work with my recorded macro. This has saved me a great deal of work. I am still having a problem though, as the Header/Footer is restricted to the first sheet in the workbook only. The program appears to select all sheets, but the Headers are not there. I have also tried recording a macro selecting all sheets as in your first reply. This works when done individualy, but not when applying the macro. The sheets are selected as in your program, but again the Header is not applied. Any sugestions? "Dave Peterson" wrote: You could record a macro when you change one of the headers/footers. Tweak it to limit it to only the stuff you need to do (delete the stuff that gets recorded that you don't want to change). Then you could modify that code to look at the worksheets in the workbooks that you want updated. Kind of like this: Option Explicit Sub testme() Dim myFileNames As Variant Dim iCtr As Long Dim wkbk As Workbook Dim wks As Worksheet myFileNames = Application.GetOpenFilename _ ("Excel Files,*.xls", MultiSelect:=True) If IsArray(myFileNames) = False Then Exit Sub 'user hit cancel End If For iCtr = LBound(myFileNames) To UBound(myFileNames) Set wkbk = Workbooks.Open(Filename:=myFileNames(iCtr)) For Each wks In wkbk.Worksheets 'fix that header/footer (your modified code here) Next wks wkbk.Close savechanges:=True Next iCtr End Sub The multiselect:=true means that you can select multiple files (click on the first, ctrl-click on subsequent) in the File|Open dialog. If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Tester wrote: Thanks Dave, but no, I need to change headers and footers in over a hundred different folders on a regular basis. As the headers/footers are all the same, I need to be able to select all the folders I want to change, and change as a batch. "Dave Peterson" wrote: maybe... If you're writing about worksheets in the same workbook, you can group the sheets (click on the first tab and ctrl-click on subsequent tabs). Then File|page setup and change the headers/footers. Remember to ungroup the sheets (just rightclick on any of the grouped sheets and select ungroup sheets) or you may damage your workbook. Almost anything you do to a one sheet (of a group) is done to the rest of the sheets in that group. Tester wrote: -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I change Headers and footers in selected Reports at the sa
I am afraid I did not make myself clear last time Dave. I do want to have
headers and footers on each sheet of every workbook. With my very limited knowledge of Visual Basic, I can see that your code calls for every sheet on each workbook to be opened, and the header/ footer code applied. Unfortunatly, this doesn't happen for me. Watching carefully while the program is running, I see that all sheets after sheet one on each workbook is not highlighted, so the header/footer is only applied to the first sheet in each workbook. "Dave Peterson" wrote: This portion loops through all the worksheets in the workbook: For Each wks In wkbk.Worksheets 'fix that header/footer (your modified code here) Next wks If you don't need that portion, just write your code to fix the single sheet: wkbk.worksheets("sheet1")... or if it's always the leftmost: wkbk.worksheets(1).... Tester wrote: Thanks for the help Dave. I am afraid that I have had very little experience with Visual Basic, but I finaly made your program work with my recorded macro. This has saved me a great deal of work. I am still having a problem though, as the Header/Footer is restricted to the first sheet in the workbook only. The program appears to select all sheets, but the Headers are not there. I have also tried recording a macro selecting all sheets as in your first reply. This works when done individualy, but not when applying the macro. The sheets are selected as in your program, but again the Header is not applied. Any sugestions? "Dave Peterson" wrote: You could record a macro when you change one of the headers/footers. Tweak it to limit it to only the stuff you need to do (delete the stuff that gets recorded that you don't want to change). Then you could modify that code to look at the worksheets in the workbooks that you want updated. Kind of like this: Option Explicit Sub testme() Dim myFileNames As Variant Dim iCtr As Long Dim wkbk As Workbook Dim wks As Worksheet myFileNames = Application.GetOpenFilename _ ("Excel Files,*.xls", MultiSelect:=True) If IsArray(myFileNames) = False Then Exit Sub 'user hit cancel End If For iCtr = LBound(myFileNames) To UBound(myFileNames) Set wkbk = Workbooks.Open(Filename:=myFileNames(iCtr)) For Each wks In wkbk.Worksheets 'fix that header/footer (your modified code here) Next wks wkbk.Close savechanges:=True Next iCtr End Sub The multiselect:=true means that you can select multiple files (click on the first, ctrl-click on subsequent) in the File|Open dialog. If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Tester wrote: Thanks Dave, but no, I need to change headers and footers in over a hundred different folders on a regular basis. As the headers/footers are all the same, I need to be able to select all the folders I want to change, and change as a batch. "Dave Peterson" wrote: maybe... If you're writing about worksheets in the same workbook, you can group the sheets (click on the first tab and ctrl-click on subsequent tabs). Then File|page setup and change the headers/footers. Remember to ungroup the sheets (just rightclick on any of the grouped sheets and select ungroup sheets) or you may damage your workbook. Almost anything you do to a one sheet (of a group) is done to the rest of the sheets in that group. Tester wrote: -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I change Headers and footers in selected Reports at the sa
I'm guessing that it was something that happened after you merged that original
shell and your macro that changed the header/footer. First, ignore the followup and go back to the original suggestion. But my guess is that your code is working on the activesheet (if you recorded the macro, this is a common problem). Option Explicit Sub testme() Dim myFileNames As Variant Dim iCtr As Long Dim wkbk As Workbook Dim wks As Worksheet myFileNames = Application.GetOpenFilename _ ("Excel Files,*.xls", MultiSelect:=True) If IsArray(myFileNames) = False Then Exit Sub 'user hit cancel End If For iCtr = LBound(myFileNames) To UBound(myFileNames) Set wkbk = Workbooks.Open(Filename:=myFileNames(iCtr)) For Each wks In wkbk.Worksheets 'fix that header/footer (your modified code here) Next wks wkbk.Close savechanges:=True Next iCtr End Sub Replace that 'fix that header... line with your code. But if you have any: with activesheet change them to with wks In fact, if you have any activesheet's at all, replace them all with wks. Another option... For Each wks In wkbk.Worksheets wks.activate 'fix that header/footer (your modified code here) Next wks By adding that wks.activate line, it may be sufficient. If all else fails, you're going to have to post the code you're using. Tester wrote: I am afraid I did not make myself clear last time Dave. I do want to have headers and footers on each sheet of every workbook. With my very limited knowledge of Visual Basic, I can see that your code calls for every sheet on each workbook to be opened, and the header/ footer code applied. Unfortunatly, this doesn't happen for me. Watching carefully while the program is running, I see that all sheets after sheet one on each workbook is not highlighted, so the header/footer is only applied to the first sheet in each workbook. "Dave Peterson" wrote: This portion loops through all the worksheets in the workbook: For Each wks In wkbk.Worksheets 'fix that header/footer (your modified code here) Next wks If you don't need that portion, just write your code to fix the single sheet: wkbk.worksheets("sheet1")... or if it's always the leftmost: wkbk.worksheets(1).... Tester wrote: Thanks for the help Dave. I am afraid that I have had very little experience with Visual Basic, but I finaly made your program work with my recorded macro. This has saved me a great deal of work. I am still having a problem though, as the Header/Footer is restricted to the first sheet in the workbook only. The program appears to select all sheets, but the Headers are not there. I have also tried recording a macro selecting all sheets as in your first reply. This works when done individualy, but not when applying the macro. The sheets are selected as in your program, but again the Header is not applied. Any sugestions? "Dave Peterson" wrote: You could record a macro when you change one of the headers/footers. Tweak it to limit it to only the stuff you need to do (delete the stuff that gets recorded that you don't want to change). Then you could modify that code to look at the worksheets in the workbooks that you want updated. Kind of like this: Option Explicit Sub testme() Dim myFileNames As Variant Dim iCtr As Long Dim wkbk As Workbook Dim wks As Worksheet myFileNames = Application.GetOpenFilename _ ("Excel Files,*.xls", MultiSelect:=True) If IsArray(myFileNames) = False Then Exit Sub 'user hit cancel End If For iCtr = LBound(myFileNames) To UBound(myFileNames) Set wkbk = Workbooks.Open(Filename:=myFileNames(iCtr)) For Each wks In wkbk.Worksheets 'fix that header/footer (your modified code here) Next wks wkbk.Close savechanges:=True Next iCtr End Sub The multiselect:=true means that you can select multiple files (click on the first, ctrl-click on subsequent) in the File|Open dialog. If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Tester wrote: Thanks Dave, but no, I need to change headers and footers in over a hundred different folders on a regular basis. As the headers/footers are all the same, I need to be able to select all the folders I want to change, and change as a batch. "Dave Peterson" wrote: maybe... If you're writing about worksheets in the same workbook, you can group the sheets (click on the first tab and ctrl-click on subsequent tabs). Then File|page setup and change the headers/footers. Remember to ungroup the sheets (just rightclick on any of the grouped sheets and select ungroup sheets) or you may damage your workbook. Almost anything you do to a one sheet (of a group) is done to the rest of the sheets in that group. Tester wrote: -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I change Headers and footers in selected Reports at the sa
Dave your were spot on! I changed the Activesheets to Wks. and it now workd
fine. Thanks once again for all the help, you have saved me a great deal of work "Dave Peterson" wrote: I'm guessing that it was something that happened after you merged that original shell and your macro that changed the header/footer. First, ignore the followup and go back to the original suggestion. But my guess is that your code is working on the activesheet (if you recorded the macro, this is a common problem). Option Explicit Sub testme() Dim myFileNames As Variant Dim iCtr As Long Dim wkbk As Workbook Dim wks As Worksheet myFileNames = Application.GetOpenFilename _ ("Excel Files,*.xls", MultiSelect:=True) If IsArray(myFileNames) = False Then Exit Sub 'user hit cancel End If For iCtr = LBound(myFileNames) To UBound(myFileNames) Set wkbk = Workbooks.Open(Filename:=myFileNames(iCtr)) For Each wks In wkbk.Worksheets 'fix that header/footer (your modified code here) Next wks wkbk.Close savechanges:=True Next iCtr End Sub Replace that 'fix that header... line with your code. But if you have any: with activesheet change them to with wks In fact, if you have any activesheet's at all, replace them all with wks. Another option... For Each wks In wkbk.Worksheets wks.activate 'fix that header/footer (your modified code here) Next wks By adding that wks.activate line, it may be sufficient. If all else fails, you're going to have to post the code you're using. Tester wrote: I am afraid I did not make myself clear last time Dave. I do want to have headers and footers on each sheet of every workbook. With my very limited knowledge of Visual Basic, I can see that your code calls for every sheet on each workbook to be opened, and the header/ footer code applied. Unfortunatly, this doesn't happen for me. Watching carefully while the program is running, I see that all sheets after sheet one on each workbook is not highlighted, so the header/footer is only applied to the first sheet in each workbook. "Dave Peterson" wrote: This portion loops through all the worksheets in the workbook: For Each wks In wkbk.Worksheets 'fix that header/footer (your modified code here) Next wks If you don't need that portion, just write your code to fix the single sheet: wkbk.worksheets("sheet1")... or if it's always the leftmost: wkbk.worksheets(1).... Tester wrote: Thanks for the help Dave. I am afraid that I have had very little experience with Visual Basic, but I finaly made your program work with my recorded macro. This has saved me a great deal of work. I am still having a problem though, as the Header/Footer is restricted to the first sheet in the workbook only. The program appears to select all sheets, but the Headers are not there. I have also tried recording a macro selecting all sheets as in your first reply. This works when done individualy, but not when applying the macro. The sheets are selected as in your program, but again the Header is not applied. Any sugestions? "Dave Peterson" wrote: You could record a macro when you change one of the headers/footers. Tweak it to limit it to only the stuff you need to do (delete the stuff that gets recorded that you don't want to change). Then you could modify that code to look at the worksheets in the workbooks that you want updated. Kind of like this: Option Explicit Sub testme() Dim myFileNames As Variant Dim iCtr As Long Dim wkbk As Workbook Dim wks As Worksheet myFileNames = Application.GetOpenFilename _ ("Excel Files,*.xls", MultiSelect:=True) If IsArray(myFileNames) = False Then Exit Sub 'user hit cancel End If For iCtr = LBound(myFileNames) To UBound(myFileNames) Set wkbk = Workbooks.Open(Filename:=myFileNames(iCtr)) For Each wks In wkbk.Worksheets 'fix that header/footer (your modified code here) Next wks wkbk.Close savechanges:=True Next iCtr End Sub The multiselect:=true means that you can select multiple files (click on the first, ctrl-click on subsequent) in the File|Open dialog. If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Tester wrote: Thanks Dave, but no, I need to change headers and footers in over a hundred different folders on a regular basis. As the headers/footers are all the same, I need to be able to select all the folders I want to change, and change as a batch. "Dave Peterson" wrote: maybe... If you're writing about worksheets in the same workbook, you can group the sheets (click on the first tab and ctrl-click on subsequent tabs). Then File|page setup and change the headers/footers. Remember to ungroup the sheets (just rightclick on any of the grouped sheets and select ungroup sheets) or you may damage your workbook. Almost anything you do to a one sheet (of a group) is done to the rest of the sheets in that group. Tester wrote: -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
headers and footers | Excel Discussion (Misc queries) | |||
Connect document properties to headers and footers | Excel Discussion (Misc queries) | |||
How do I set up global headers and footers in Excel? | Setting up and Configuration of Excel | |||
If I change page margins headers don't line up??? | Excel Discussion (Misc queries) | |||
Font Colors with headers and Footers | Excel Discussion (Misc queries) |