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

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

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