Hi All,

I have got a data which is regularly updated by my colleagues in Excel file
which is a shared file saved in a share drive.

There are around 35 to 40 worksheet. One worksheet for each employees. It
has 9 columns. I want a macro which will import data entered in all these 35
to 40 worksheet into one master worksheet of the same or another file. Where
in I can prepare some pivot tables to do some analysis.

Is it possible to get all the data from these many worksheet into one master
worksheet by click of a button.

Cheers,
Mandeep

Hello Mandeep

sure it is possible.

You can go through every worksheet with a for loop:

dim ws as worksheet

for each ws in worksheets
'here comes the code to copy your data
next ws

if you want to exclude your Mainsheet you have to do it like that

for each ws in worksheets
if ws.name <> "mainsheet" then
'here comes the code to copy your data
end if
next ws

hope that is clear...i don't know how your layout looks like, and i
don't know how you want to arrange it on the mainsheet so that's how
far i can help you. If you want more information, please ask.

Hth

Carlo



On Dec 21, 2:41*pm, Mandeep Dhami
<MandeepDh...@discussions.microsoft.com> wrote:
> Hi All,
>
> I have got a data which is regularly updated by my colleagues in Excel file
> which is a shared file saved in a share drive.
>
> There are around 35 to 40 worksheet. One worksheet for each employees. It
> has 9 columns. I want a macro which will import data entered in all these 35
> to 40 worksheet into one master worksheet of the same or another file. Where
> in I can prepare some pivot tables to do some analysis.
>
> Is it possible to get all the data from these many worksheet into one master
> worksheet by click of a button.
>
> Cheers,
> Mandeep

Thanks Carlo for your help.

I am unable to understand the solution given by you. I am sending you an
attachment of the file that I am referrring to. Hope this will give you an
idea of what exactly I require.

Cheers,
Mandeep

"carlo" wrote:

> Hello Mandeep
>
> sure it is possible.
>
> You can go through every worksheet with a for loop:
>
> dim ws as worksheet
>
> for each ws in worksheets
> 'here comes the code to copy your data
> next ws
>
> if you want to exclude your Mainsheet you have to do it like that
>
> for each ws in worksheets
> if ws.name <> "mainsheet" then
> 'here comes the code to copy your data
> end if
> next ws
>
> hope that is clear...i don't know how your layout looks like, and i
> don't know how you want to arrange it on the mainsheet so that's how
> far i can help you. If you want more information, please ask.
>
> Hth
>
> Carlo
>
>
>
> On Dec 21, 2:41 pm, Mandeep Dhami
> <MandeepDh...@discussions.microsoft.com> wrote:
> > Hi All,
> >
> > I have got a data which is regularly updated by my colleagues in Excel file
> > which is a shared file saved in a share drive.
> >
> > There are around 35 to 40 worksheet. One worksheet for each employees. It
> > has 9 columns. I want a macro which will import data entered in all these 35
> > to 40 worksheet into one master worksheet of the same or another file. Where
> > in I can prepare some pivot tables to do some analysis.
> >
> > Is it possible to get all the data from these many worksheet into one master
> > worksheet by click of a button.
> >
> > Cheers,
> > Mandeep
>
>

Hi Mandeep, this is the solution:

Sub Consolidate()

Dim ws As Worksheet
Dim ws_main As Worksheet

Set ws_main = Worksheets("Master worksheet")

For Each ws In Worksheets
If ws.Name <> ws_main.Name Then
'actual copying
ws.Range("a2:i" & ws.Cells(65536, 1).End(xlUp).Row).Copy
ws_main.Range("a" & ws_main.Cells(65536, 1).End(xlUp).Row + 1)
End If
Next ws

End Sub


hth

Carlo


On Dec 21, 3:53*pm, Mandeep Dhami
<MandeepDh...@discussions.microsoft.com> wrote:
> Thanks Carlo for your help.
>
> I am unable to understand the solution given by you. I am sending you an
> attachment of the file that I am referrring to. Hope this will give you an
> idea of what exactly I require.
>
> Cheers,
> Mandeep
>
>
>
> "carlo" wrote:
> > Hello Mandeep
>
> > sure it is possible.
>
> > You can go through every worksheet with a for loop:
>
> > dim ws as worksheet
>
> > for each ws in worksheets
> > * *'here comes the code to copy your data
> > next ws
>
> > if you want to exclude your Mainsheet you have to do it like that
>
> > for each ws in worksheets
> > * *if ws.name <> "mainsheet" then
> > * * * 'here comes the code to copy your data
> > * *end if
> > next ws
>
> > hope that is clear...i don't know how your layout looks like, and i
> > don't know how you want to arrange it on the mainsheet so that's how
> > far i can help you. If you want more information, please ask.
>
> > Hth
>
> > Carlo
>
> > On Dec 21, 2:41 pm, Mandeep Dhami
> > <MandeepDh...@discussions.microsoft.com> wrote:
> > > Hi All,
>
> > > I have got a data which is regularly updated by my colleagues in Excelfile
> > > which is a shared file saved in a share drive.
>
> > > There are around 35 to 40 worksheet. One worksheet for each employees.It
> > > has 9 columns. I want a macro which will import data entered in all these 35
> > > to 40 worksheet into one master worksheet of the same or another file.Where
> > > in I can prepare some pivot tables to do some analysis.
>
> > > Is it possible to get all the data from these many worksheet into one master
> > > worksheet by click of a button.
>
> > > Cheers,
> > > Mandeep- Hide quoted text -
>
> - Show quoted text -