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