Consolidating data from various worksheet of same excel file

[Mandeep Dhami]

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

 

[carlo]

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

 

[Mandeep Dhami]

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]

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