Merging Worksheets
:rolleyes: I'll be glad when I can get my head round this.... but until then I have to rely on you good people! Further to my question of earlier today, I now have all my worksheets merging thanks to some much appreciated advice, however, the method I'm using also merges the data that has been merged into the Master Worksheet! 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 Once again, thank you for your time, advice and patience Regards, Dave -- deelee ------------------------------------------------------------------------ deelee's Profile: http://www.excelforum.com/member.php...o&userid=34866 View this thread: http://www.excelforum.com/showthread...hreadid=556697 |
All times are GMT +1. The time now is 04:28 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com