A macro for data consolidation

[Guest]

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

 

[Ron de Bruin]

See
http://www.rondebruin.nl/copy3.htm

 

[Guest]

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?

 

[Ron de Bruin]

Hi SR

Read the info on my site about the reference
http://www.rondebruin.nl/ado.htm

 

[Guest]

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

 

[Guest]

Thanks, i got it. i can run the macro with a hidden sheet. thanks again

 

[Ron de Bruin]

Hi SR

With Sheets("Sheet1")
.Visible = True
.Select
MsgBox "Hi"
.Visible = False
End With