Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
A couple of questions (formating & Collating worksheets)
Morning all, So far I have managed to insert a new worksheet from a hidde 'template'; unhide it and through a UserForm, change the name of tha worksheet; in addition I have managed to place the name of th worksheet in a given cell (worksheets are indiviualised to staf members and I need their name to appear for a formula to work) - so fa so good! My problems are two fold; 1. I need the worksheet name to appear in the header when printing (th user's name) but no matter how I try I can't get it to work, here is m line of code: .CenterHeader = _ "&""Arial,Bold""&11Sheet name here!" & Chr(10) & "" & Chr(10) "&10POther text here" This obviously places 'Sheet name here' as the first line of the heade and I need the ActiveSheet.Name to appear! 2. I collate all remaing worksheets into one called 'Master' and have another worksheet hidden called "Template". Currently I use the following code to collate the remainin worksheets: 'loop thru an array of sheets For Each wsSrc In wb.Worksheets( _ Array("Carrie", "Dave", "Eileen", "Geraldine", "Jabeen", "Jackie" "Julie", "Pauline", "Rebecca")) 'alt: array(2,3,4,5) How do I add additional sheets to this when they are created i.e sa one called "Joe" Sorry for the long post but I only need to have these cracked for m project to work much better! (always enhancing :) ) Thank you, Dav -- deele ----------------------------------------------------------------------- deelee's Profile: http://www.excelforum.com/member.php...fo&userid=3486 View this thread: http://www.excelforum.com/showthread.php?threadid=55639 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
A couple of questions (formating & Collating worksheets)
Hi Dave
1. sLine1 = ActiveSheet.Name sLine2 = Range("A1").Text ..CenterHeader = _ "&""Arial,Bold""&11" & sLine1 & Chr(10) & Chr(10) & sLine2 2 You have hard-coded your array of sheet names so you can't change it. If you want to process all worksheets simply For each ws in activeworkbook.worksheets Or for specific sheets perhaps maintain a list in a dynamic named range (another subject) on a hidden sheet (these can also be updated with 'certain worksheet formulas' if names change) Dim vNames as variant vNames = Application.Transpose(Range("namedrange")) For Each sh In Worksheets(vNames) or populate a Redim'd 1D horizontal array of names obtained from elsewhere Regards, Peter T "deelee" wrote in message ... Morning all, So far I have managed to insert a new worksheet from a hidden 'template'; unhide it and through a UserForm, change the name of that worksheet; in addition I have managed to place the name of the worksheet in a given cell (worksheets are indiviualised to staff members and I need their name to appear for a formula to work) - so far so good! My problems are two fold; 1. I need the worksheet name to appear in the header when printing (the user's name) but no matter how I try I can't get it to work, here is my line of code: CenterHeader = _ "&""Arial,Bold""&11Sheet name here!" & Chr(10) & "" & Chr(10) & "&10POther text here" This obviously places 'Sheet name here' as the first line of the header and I need the ActiveSheet.Name to appear! 2. I collate all remaing worksheets into one called 'Master' and I have another worksheet hidden called "Template". Currently I use the following code to collate the remaining worksheets: 'loop thru an array of sheets For Each wsSrc In wb.Worksheets( _ Array("Carrie", "Dave", "Eileen", "Geraldine", "Jabeen", "Jackie", "Julie", "Pauline", "Rebecca")) 'alt: array(2,3,4,5) How do I add additional sheets to this when they are created i.e say one called "Joe" Sorry for the long post but I only need to have these cracked for my project to work much better! (always enhancing :) ) Thank you, Dave -- deelee ------------------------------------------------------------------------ deelee's Profile: http://www.excelforum.com/member.php...o&userid=34866 View this thread: http://www.excelforum.com/showthread...hreadid=556391 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
A couple of questions (formating & Collating worksheets)
Hi Peter, thanks for your reply and help - I've resolved my 1st problem with your suggestion, however, I'm still stuck with collating the sheets. I 'borrowed' the code to do this and don't really understand it (haven't got to grips with VBA!). The code I've used is below and I just inserted this and it worked (with a few trial and error adjustments)! 'Clean the destination... (leave 1 headerrow) wsDst.UsedRange.Offset(2).ClearContents 'loop thru an array of sheets For Each wsSrc In wb.Worksheets( _ Array("Carrie", "Dave", "Eileen", "Geraldine", "Jabeen", "Jackie", "Julie", "Pauline", "Rebecca")) 'alt: array(2,3,4,5) 'find the current region starting at cell A1 Set rgSrc = wsSrc.Cells(1).CurrentRegion 'shift 1 row down to skip the headers. Set rgSrc = rgSrc.Resize(rgSrc.Rows.Count - 1).Offset(1) 'find the last value in column A on master Set rgDst = wsDst.Cells(Rows.Count, 1).End(xlUp) 'shift 1 row down and size same as source Set rgDst = rgDst.Resize( _ rgSrc.Rows.Count, rgSrc.Columns.Count).Offset(1) 'copy the values rgDst.Value = rgSrc.Value Next I would love to be able to collate all worksheets but I have a Master where the collation's done and the hidden Template for my new users Any suggestions would be welcome. Regards, Dave -- deelee ------------------------------------------------------------------------ deelee's Profile: http://www.excelforum.com/member.php...o&userid=34866 View this thread: http://www.excelforum.com/showthread...hreadid=556391 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
A couple of questions (formating & Collating worksheets)
Did you get anywhere with my suggestion re 2 as related to your OP. From
what you post below seems you haven't tackled that, you have left in the same hard coded array and moved on to a different question, I think how to determine which part pf the sheet you want to print Your overall task to collate and print your sheets involves a number of separate tasks (not necessarily in this order - set the page setup header (resolved) - define the sheets you want to work, might mean maintaining a hidden list somewhere in the wb or some other method of looping through sheets to see which you need - Return a single range from each of these sheets to define the print area. It might be worth posting the above as separate questions explaining what you have and what you want to do. Then perhaps another to piece it all together. Regards, Peter T "deelee" wrote in message ... Hi Peter, thanks for your reply and help - I've resolved my 1st problem with your suggestion, however, I'm still stuck with collating the sheets. I 'borrowed' the code to do this and don't really understand it (haven't got to grips with VBA!). The code I've used is below and I just inserted this and it worked (with a few trial and error adjustments)! 'Clean the destination... (leave 1 headerrow) wsDst.UsedRange.Offset(2).ClearContents 'loop thru an array of sheets For Each wsSrc In wb.Worksheets( _ Array("Carrie", "Dave", "Eileen", "Geraldine", "Jabeen", "Jackie", "Julie", "Pauline", "Rebecca")) 'alt: array(2,3,4,5) 'find the current region starting at cell A1 Set rgSrc = wsSrc.Cells(1).CurrentRegion 'shift 1 row down to skip the headers. Set rgSrc = rgSrc.Resize(rgSrc.Rows.Count - 1).Offset(1) 'find the last value in column A on master Set rgDst = wsDst.Cells(Rows.Count, 1).End(xlUp) 'shift 1 row down and size same as source Set rgDst = rgDst.Resize( _ rgSrc.Rows.Count, rgSrc.Columns.Count).Offset(1) 'copy the values rgDst.Value = rgSrc.Value Next I would love to be able to collate all worksheets but I have a Master where the collation's done and the hidden Template for my new users Any suggestions would be welcome. Regards, Dave -- deelee ------------------------------------------------------------------------ deelee's Profile: http://www.excelforum.com/member.php...o&userid=34866 View this thread: http://www.excelforum.com/showthread...hreadid=556391 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
A couple of questions (formating & Collating worksheets)
Hi Peter - you are a saviour! You are correct, you did resolve the first of my questions and I hav tried your suggestion with respect to merging...but! The code I currently have is: Below is my code and what I need is to exclude the Master from th merge - can this be done? (sorry about all the comments but I find need to know what's going on! ) Sub ValuesToMaster() Dim wb As Workbook Dim wsSrc As Worksheet Dim wsDst As Worksheet Dim rgSrc As Range Dim rgDst As Range Set wb = ThisWorkbook 'Prevent screen updating Application.ScreenUpdating = False 'UnProtect Workbook wb.Unprotect ("deelee") 'Unprotect all worksheets For Each wsSrc In ThisWorkbook.Sheets wsDst.Unprotect ("deelee") Next 'Set destination sheet as 'Master' Set wsSrc = wb.Worksheets("Master") 'Clean the destination... (leave 2 headerrow) wsDst.UsedRange.Offset(2).ClearContents 'loop thru an array of sheets For Each wsSrc In ActiveWorkbook.Worksheets 'find the current region starting at cell A1 Set rgSrc = wsSrc.Cells(1).CurrentRegion 'shift 1 row down to skip the headers. Set rgSrc = rgSrc.Resize(rgSrc.Rows.Count - 1).Offset(1) 'find the last value in column A on master Set rgDst = wsDst.Cells(Rows.Count, 1).End(xlUp) 'shift 1 row down and size same as source Set rgDst = rgDst.Resize( _ rgSrc.Rows.Count, rgSrc.Columns.Count).Offset(1) 'copy the values rgDst.Value = rgSrc.Value Next 'Protect all Worksheets in Workbook For Each wsSrc In ThisWorkbook.Sheets wsSrc.Protect ("deelee") Next 'Update Screen Application.ScreenUpdating = True End Sub This does the job with one problem - it also merges tha Master Shee that holds all the merged information! Is there a way of excluding the 'Master' from the merge? I'd be grateful for your input. By the way, please excuse all my comments but as a novice I feel I nee to know what I'm doing! Regards, Dav -- deele ----------------------------------------------------------------------- deelee's Profile: http://www.excelforum.com/member.php...fo&userid=3486 View this thread: http://www.excelforum.com/showthread.php?threadid=55639 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
A couple of questions (formating & Collating worksheets)
I haven't gone through your code but for this -
Is there a way of excluding the 'Master' from the merge? For Each wsSrc In ThisWorkbook.Worksheets If wsSrc.Name < "Master" Then ' process all worksheets excep "Master" here Else ' anything you want to do with "Master" here End If Next Regards, Peter T "deelee" wrote in message ... Hi Peter - you are a saviour! You are correct, you did resolve the first of my questions and I have tried your suggestion with respect to merging...but! The code I currently have is: Below is my code and what I need is to exclude the Master from the merge - can this be done? (sorry about all the comments but I find I need to know what's going on! ) Sub ValuesToMaster() Dim wb As Workbook Dim wsSrc As Worksheet Dim wsDst As Worksheet Dim rgSrc As Range Dim rgDst As Range Set wb = ThisWorkbook 'Prevent screen updating Application.ScreenUpdating = False 'UnProtect Workbook wb.Unprotect ("deelee") 'Unprotect all worksheets For Each wsSrc In ThisWorkbook.Sheets wsDst.Unprotect ("deelee") Next 'Set destination sheet as 'Master' Set wsSrc = wb.Worksheets("Master") 'Clean the destination... (leave 2 headerrow) wsDst.UsedRange.Offset(2).ClearContents 'loop thru an array of sheets For Each wsSrc In ActiveWorkbook.Worksheets 'find the current region starting at cell A1 Set rgSrc = wsSrc.Cells(1).CurrentRegion 'shift 1 row down to skip the headers. Set rgSrc = rgSrc.Resize(rgSrc.Rows.Count - 1).Offset(1) 'find the last value in column A on master Set rgDst = wsDst.Cells(Rows.Count, 1).End(xlUp) 'shift 1 row down and size same as source Set rgDst = rgDst.Resize( _ rgSrc.Rows.Count, rgSrc.Columns.Count).Offset(1) 'copy the values rgDst.Value = rgSrc.Value Next 'Protect all Worksheets in Workbook For Each wsSrc In ThisWorkbook.Sheets wsSrc.Protect ("deelee") Next 'Update Screen Application.ScreenUpdating = True End Sub This does the job with one problem - it also merges tha Master Sheet that holds all the merged information! Is there a way of excluding the 'Master' from the merge? I'd be grateful for your input. By the way, please excuse all my comments but as a novice I feel I need to know what I'm doing! Regards, Dave -- deelee ------------------------------------------------------------------------ deelee's Profile: http://www.excelforum.com/member.php...o&userid=34866 View this thread: http://www.excelforum.com/showthread...hreadid=556391 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
couple of questions | Excel Discussion (Misc queries) | |||
Couple of Questions | Excel Discussion (Misc queries) | |||
A couple of questions... | Excel Discussion (Misc queries) | |||
Couple more questions... | New Users to Excel | |||
A couple of questions | Excel Programming |