Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
how do i append 4 differnt worksheets in a new master work sheets
i have got 4 work sheets with same column name e.g.
sheet 1 o/b I/b 3 4 5 6 27/04/05 89 89 233 11779 11779 11779 28/04/05 9999 9999 23324 23324 23324 11755 sheet2 o/b I/b 3 4 5 6 27/04/05 9999 9999 11779 11766 23324 23324 28/04/05 100 89 233 11791 11791 11791 so on what i want is append all these sheet in a maste sheet with the resul like master sheet o/b I/b 3 4 5 6 27/04/05 89 89 233 11779 11779 11779 28/04/05 9999 9999 23324 23324 23324 11755 27/04/05 9999 9999 11779 11766 23324 23324 28/04/05 100 89 233 11791 11791 11791 is it possible..... one more thing all are with formulas i want values in master sheet. thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
how do i append 4 differnt worksheets in a new master work sheets
open VBE: alt F11 insert a module copy and paste following macro. edit the code to use the proper names!! Option Explicit 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 'alt: Set wb = ActiveWorkbook Set wsDst = wb.Worksheets("Master") 'Clean the destination... (leave 1 headerrow) wsDst.UsedRange.Offset(1).ClearContents 'loop thru an array of sheets For Each wsSrc In wb.Worksheets( _ Array("Data1", "Data2", "Data3", "Data4")) '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 End Sub -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam darsg wrote : i have got 4 work sheets with same column name e.g. sheet 1 o/b I/b 3 4 5 6 27/04/05 89 89 233 11779 11779 11779 28/04/05 9999 9999 23324 23324 23324 11755 sheet2 o/b I/b 3 4 5 6 27/04/05 9999 9999 11779 11766 23324 23324 28/04/05 100 89 233 11791 11791 11791 so on what i want is append all these sheet in a maste sheet with the resul like master sheet o/b I/b 3 4 5 6 27/04/05 89 89 233 11779 11779 11779 28/04/05 9999 9999 23324 23324 23324 11755 27/04/05 9999 9999 11779 11766 23324 23324 28/04/05 100 89 233 11791 11791 11791 is it possible..... one more thing all are with formulas i want values in master sheet. thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
how do i append 4 differnt worksheets in a new master work she
thanks for your reply ,but i am very new for macro
can you specify this macro ,in little deft. if my work sheet name is different and everyfile is having different worksheets i mean sometimes it has 4 some times it has 6 worksheets, is it possible to make default macro, which contain common things for all files, and just run on active file which can make a master table thanks "keepITcool" wrote: open VBE: alt F11 insert a module copy and paste following macro. edit the code to use the proper names!! Option Explicit 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 'alt: Set wb = ActiveWorkbook Set wsDst = wb.Worksheets("Master") 'Clean the destination... (leave 1 headerrow) wsDst.UsedRange.Offset(1).ClearContents 'loop thru an array of sheets For Each wsSrc In wb.Worksheets( _ Array("Data1", "Data2", "Data3", "Data4")) '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 End Sub -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam darsg wrote : i have got 4 work sheets with same column name e.g. sheet 1 o/b I/b 3 4 5 6 27/04/05 89 89 233 11779 11779 11779 28/04/05 9999 9999 23324 23324 23324 11755 sheet2 o/b I/b 3 4 5 6 27/04/05 9999 9999 11779 11766 23324 23324 28/04/05 100 89 233 11791 11791 11791 so on what i want is append all these sheet in a maste sheet with the resul like master sheet o/b I/b 3 4 5 6 27/04/05 89 89 233 11779 11779 11779 28/04/05 9999 9999 23324 23324 23324 11755 27/04/05 9999 9999 11779 11766 23324 23324 28/04/05 100 89 233 11791 11791 11791 is it possible..... one more thing all are with formulas i want values in master sheet. thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Making a Pivot chart from 3 differnt sets of data / worksheets. | Charts and Charting in Excel | |||
Cell referencing between differnt worksheets | Excel Discussion (Misc queries) | |||
formula adding cells in worksheets when # of sheets in work book changes | Excel Discussion (Misc queries) | |||
How to Append the Data to the Master Table | Excel Worksheet Functions | |||
Allocate Files to Sheets and Build a Master Sheet which Summarises All Sheets | Excel Programming |