Copy pasting with reference from 2 cells and several workbooks.

[looney]

Hi!

I need help with copying a range of cells (E9:Q30) from several workbooks in a folder "A" to a main file. I'll try to explain the conditions.

* The main file contains a main sheet for a particular week and has several tabs/sheets that is similar to a specific sheet in the workbooks in folder "A".
- The same sheets ( in the main file has following information that I would like to use to specify where to look for in the workbooks in folder "A".
Cell B2 "contains the name of the workbook in folder "A" that should correspondence with sheet in the main file.
Cell B3 has got a week number that should correspondence with the right sheet from the workbook in folder "A".
Range (E9:Q30) should be the same in the main file.
* the workbookfiles in folder "A" contains a main sheet and 52 data sheets named Week 1, Week 2, ..... Week 52.


To make the structure more clear... maybe the following will help.

Folder: C:\CCC\CCC\

File: Main.xls (2003version)

Sheets: main / Name 1 / Name 2 / name 3..../..../ etc.

------------------------------------------------------

Folder: C:\CCC\CCC\CC\

Files: Name 1.xls / Name 2.xls / Name 3.xls / .... .xls /... .xls etc.

Sheets (same for every file in this folder): Week 1 / Week 2 / Week 3 / .../.../ Week 52



At the moment every week the sheets are copy pasted from the workbooks to the main file.

I'd like to change this so the only input is needed Is a week number I fill in on the main sheet of the main file and just have to push a macro button.

I'd tried to work with a formula =INDIRECT("'C:\CCC\CCC\CC\["&$B$2&".xls]"&"Week "&$B$3&"'!"&"E9") That works fine, but the disadvantages were that it would run very slow because of the formula with little tweaks would be in every cell in the range (E9:Q30). Also every workbook needed to be opened and closed which I did not put in a macro yet.

So I want to try a new approach and make use of the week numbers and the names in Cell B2 an B3 and just copy paste this parts from the CC folder workbooks to the main file with a macro/VBA. This needs to be done weekly any help in this would be very much appreciated.

Thanks in advance!

[looney]

Anyone an idea where to start with? or point me out in the right direction?

I already have been searching online, but with no good results...

[looney]

Hi,

I still hope to get some help with this....

I am getting lost..

I have found some macro coding that could help me out a bit.

Please Login or Register  to view this content.

In the next part:
Const SheetName$ = "Week 21"
I would like to refer to a cell on the main sheet where I would fill in the data manually

I might could do the same for:
Const FileName$ = "Name 1.xls"
But then would like to refer to a list of names or something.

Please help me out. I am just a beginner in Macro's.