Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Consolidating data from various worksheet of same excel file
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Consolidating data from various worksheet of same excel file
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 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Consolidating data from various worksheet of same excel file
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 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Consolidating data from various worksheet of same excel file
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 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 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- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macros for Consolidating data in worksheet 1 on worksheet 2 | Excel Programming | |||
consolidating data in an excel worksheet | Excel Discussion (Misc queries) | |||
Consolidating Sales Data to Summary Worksheet | Excel Worksheet Functions | |||
Consolidating Data to one worksheet | Excel Programming | |||
Need advice : consolidating data from multiple CSV files in Excel - External data handling | Excel Programming |