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