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

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

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
>