| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
A macro for data consolidation
I might sound like I need spoon feeding but I know very little about VB code.
I have "n" number of xcel files of same format and sheet name. data I need to consolidate is from col B from 2 sheets of each file. I want to create a summary report from all these files. I have created a summary xcel file where i have created two sheets and manually copy col B from each files. then put some formula in Summary sheet to calculate and produce the report. I want to create a macro to avoid these manual copy/paste. not definite number of files. so the macro should open all the files with the "bbbbbbbb*.xls" from a particular folder. and copy col b from each of two sheet to the two destination sheet in the summary file and then close and repeat this for all the open files. Can some one help please -- sr |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
A macro for data consolidation
See
http://www.rondebruin.nl/copy3.htm -- Regards Ron de Bruin http://www.rondebruin.nl "SR" wrote in message ... I might sound like I need spoon feeding but I know very little about VB code. I have "n" number of xcel files of same format and sheet name. data I need to consolidate is from col B from 2 sheets of each file. I want to create a summary report from all these files. I have created a summary xcel file where i have created two sheets and manually copy col B from each files. then put some formula in Summary sheet to calculate and produce the report. I want to create a macro to avoid these manual copy/paste. not definite number of files. so the macro should open all the files with the "bbbbbbbb*.xls" from a particular folder. and copy col b from each of two sheet to the two destination sheet in the summary file and then close and repeat this for all the open files. Can some one help please -- sr |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
A macro for data consolidation
Thanks, I am trying use your coes to get my obj. while running the macro i
faced error on following line in getdata macro- Dim rsData As ADODB.Recordset Is it due to version mismatch? -- sr "Ron de Bruin" wrote: See http://www.rondebruin.nl/copy3.htm -- Regards Ron de Bruin http://www.rondebruin.nl "SR" wrote in message ... I might sound like I need spoon feeding but I know very little about VB code. I have "n" number of xcel files of same format and sheet name. data I need to consolidate is from col B from 2 sheets of each file. I want to create a summary report from all these files. I have created a summary xcel file where i have created two sheets and manually copy col B from each files. then put some formula in Summary sheet to calculate and produce the report. I want to create a macro to avoid these manual copy/paste. not definite number of files. so the macro should open all the files with the "bbbbbbbb*.xls" from a particular folder. and copy col b from each of two sheet to the two destination sheet in the summary file and then close and repeat this for all the open files. Can some one help please -- sr |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
A macro for data consolidation
Hi SR
Read the info on my site about the reference http://www.rondebruin.nl/ado.htm -- Regards Ron de Bruin http://www.rondebruin.nl "SR" wrote in message ... Thanks, I am trying use your coes to get my obj. while running the macro i faced error on following line in getdata macro- Dim rsData As ADODB.Recordset Is it due to version mismatch? -- sr "Ron de Bruin" wrote: See http://www.rondebruin.nl/copy3.htm -- Regards Ron de Bruin http://www.rondebruin.nl "SR" wrote in message ... I might sound like I need spoon feeding but I know very little about VB code. I have "n" number of xcel files of same format and sheet name. data I need to consolidate is from col B from 2 sheets of each file. I want to create a summary report from all these files. I have created a summary xcel file where i have created two sheets and manually copy col B from each files. then put some formula in Summary sheet to calculate and produce the report. I want to create a macro to avoid these manual copy/paste. not definite number of files. so the macro should open all the files with the "bbbbbbbb*.xls" from a particular folder. and copy col b from each of two sheet to the two destination sheet in the summary file and then close and repeat this for all the open files. Can some one help please -- sr |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
A macro for data consolidation
Thanks Ron. I got it.
One more question- Could you please tell me the syntax for unhide a worksheet and then hide it back. that will help me complete my macro -- sr "Ron de Bruin" wrote: Hi SR Read the info on my site about the reference http://www.rondebruin.nl/ado.htm -- Regards Ron de Bruin http://www.rondebruin.nl "SR" wrote in message ... Thanks, I am trying use your coes to get my obj. while running the macro i faced error on following line in getdata macro- Dim rsData As ADODB.Recordset Is it due to version mismatch? -- sr "Ron de Bruin" wrote: See http://www.rondebruin.nl/copy3.htm -- Regards Ron de Bruin http://www.rondebruin.nl "SR" wrote in message ... I might sound like I need spoon feeding but I know very little about VB code. I have "n" number of xcel files of same format and sheet name. data I need to consolidate is from col B from 2 sheets of each file. I want to create a summary report from all these files. I have created a summary xcel file where i have created two sheets and manually copy col B from each files. then put some formula in Summary sheet to calculate and produce the report. I want to create a macro to avoid these manual copy/paste. not definite number of files. so the macro should open all the files with the "bbbbbbbb*.xls" from a particular folder. and copy col b from each of two sheet to the two destination sheet in the summary file and then close and repeat this for all the open files. Can some one help please -- sr |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
A macro for data consolidation
Thanks, i got it. i can run the macro with a hidden sheet. thanks again
-- sr "SR" wrote: I might sound like I need spoon feeding but I know very little about VB code. I have "n" number of xcel files of same format and sheet name. data I need to consolidate is from col B from 2 sheets of each file. I want to create a summary report from all these files. I have created a summary xcel file where i have created two sheets and manually copy col B from each files. then put some formula in Summary sheet to calculate and produce the report. I want to create a macro to avoid these manual copy/paste. not definite number of files. so the macro should open all the files with the "bbbbbbbb*.xls" from a particular folder. and copy col b from each of two sheet to the two destination sheet in the summary file and then close and repeat this for all the open files. Can some one help please -- sr |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
A macro for data consolidation
Hi SR
With Sheets("Sheet1") .Visible = True .Select MsgBox "Hi" .Visible = False End With -- Regards Ron de Bruin http://www.rondebruin.nl "SR" wrote in message ... Thanks Ron. I got it. One more question- Could you please tell me the syntax for unhide a worksheet and then hide it back. that will help me complete my macro -- sr "Ron de Bruin" wrote: Hi SR Read the info on my site about the reference http://www.rondebruin.nl/ado.htm -- Regards Ron de Bruin http://www.rondebruin.nl "SR" wrote in message ... Thanks, I am trying use your coes to get my obj. while running the macro i faced error on following line in getdata macro- Dim rsData As ADODB.Recordset Is it due to version mismatch? -- sr "Ron de Bruin" wrote: See http://www.rondebruin.nl/copy3.htm -- Regards Ron de Bruin http://www.rondebruin.nl "SR" wrote in message ... I might sound like I need spoon feeding but I know very little about VB code. I have "n" number of xcel files of same format and sheet name. data I need to consolidate is from col B from 2 sheets of each file. I want to create a summary report from all these files. I have created a summary xcel file where i have created two sheets and manually copy col B from each files. then put some formula in Summary sheet to calculate and produce the report. I want to create a macro to avoid these manual copy/paste. not definite number of files. so the macro should open all the files with the "bbbbbbbb*.xls" from a particular folder. and copy col b from each of two sheet to the two destination sheet in the summary file and then close and repeat this for all the open files. Can some one help please -- sr |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Search, Copy, Paste Macro in Excel | Excel Worksheet Functions | |||
| Closing File Error | Excel Discussion (Misc queries) | |||
| macro with F9 | Excel Discussion (Misc queries) | |||
| Make Alignment options under format cells available as shortcut | Excel Discussion (Misc queries) | |||
| Playing a macro from another workbook | Excel Discussion (Misc queries) | |||
Linear Mode
