A couple of questions (formating & Collating worksheets)

[deelee]

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

 

[Peter T]

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]

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

 

[Peter T]

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]

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

 

[Peter T]

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]

Hi Peter - I have a new problem!

When I try the macro here at work (Excel 2000) it won't work!

I was working on my PC at home when I tried the Protect/Unprotect and
it worked; so my question is - should the macro I posted work on Excel
2000 or have I goofed somewhere?

Regards,

Dave

 

[Peter T]

Versions after XL2000 have additional protection options. Don't know if
that's the reason your code doesn't work as I only have xl2000 to hand.

Regards,
Peter T