c.value issue in for loop

[Shippwreck]

Hi,

I have 30 workbooks, all formated exactly the same and i need to take the contents of certain cells out of each of them and collate into a single workbook.

However i can't get the following piece of code to work, it keeps saying "object doesn't support this property or method"

For Each c In Workbooks(file_to_open(num) & ".xls").Worksheets("Submission").Range("B13:Q19").Cells
transfer = c.Value
Workbooks("Submission Tool.xls").Worksheets("Q19").c = transfer
Next

All 30 file names are kept in the array file_to_open and this code is surrounded in a do loop saying open and close each one with Num = Num + 1 just before the loop statement of the do.

Any ideas,

Thanks

http://www.mattandhayley.me.uk

"Google is god...of the internet" - Me

 

[PHV]

Instantiate a workbook object when you open file_to_open(num) & ".xls":
...
Set myWB = Workbooks.Open(file_to_open(num) & ".xls")
For Each c In myWB.Worksheets("Submission").Range("B13:Q19")
...
Next
myWB.Close False
Set mymyWB = Nothing
...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Shippwreck]

Ok i now have:

Do Until num = 30
Set myWB = Workbooks.Open(Path & file_to_open(num) & ".xls")

For Each c In myWB.Worksheets("Submission").Range("B13:Q19")
transfer = c.Value
Workbooks("Submission Tool.xls").Worksheets("Q19").c = transfer
Next

myWB.Close False
Set mymyWB = Nothing
num = num + 1
Loop

and the error i get is "subscript out of range"

To be honest i don't even fully understand what that bit of code you added does, so if you could explain that a little that would help to, what does instantiating a workbook object actually do/add?

Thanks

http://www.mattandhayley.me.uk

"Google is god...of the internet" - Me

 

[PHV]

and the error i get is "subscript out of range"
Any chance you could say us which line raises the above error ?

Anyway I don't understand why you need a For Each to simply do this:
Workbooks("Submission Tool.xls").Worksheets("Q19").c = transfer

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Shippwreck]

Oh,

Sorry, yeah that might help.

The line is Workbooks("Submission Tool.xls").Worksheets("Q19").c = transfer

What i need to do is take the values from every cell in the range B13 to Q19 and put them in exactly the same range in a second sheet. Now i have 30 files that i need to do this for, and the whole idea is that those 30 files will all be summed into one file.

I know that at the moment the code would just overwrite the previous one but all i would need to do is change it to something like:

Workbooks("Submission Tool.xls").Worksheets("Q19").c = Workbooks("Submission Tool.xls").Worksheets("Q19").c + transfer

If you know of a better way then please tell me, however the only other way i could see was to specifically open each file and select one cell at a time, creating a huge amount of code.

http://www.mattandhayley.me.uk

"Google is god...of the internet" - Me

 

[PHV]

How many worksheets has "Submission Tool.xls" ?
Here a starting point:
For num = 0 To 29
Set myWB = Workbooks.Open(Path & file_to_open(num) & ".xls")
myWB.Worksheets("Submission").Range("B13:Q19").Copy _
Destination:=Workbooks("Submission Tool.xls").Worksheets(some sheet name here).Range("B13")
myWB.Close False
Set myWB = Nothing
Next

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Shippwreck]

Ah,

I have just realised that the c in the statement actually takes the value of the cell, not the cell reference, which is what i thought it was doing. (its the first time i've tried to use a for loop like this)

Maybe that explains why you can't understand what i'm doing cause i'm trying to do something that doesn't make sense...

I think the post above explains what i want to do so hopefully you might be able to help from there.

Thanks

http://www.mattandhayley.me.uk

"Google is god...of the internet" - Me

 

[Shippwreck]

Surely, all that would do is just a copy and paste, and so when it gets to the next file it will just overwrite, and not add to the existing cells.

Anyway i have it working now, the code reads

For Each c In myWB.Worksheets("Submission").Range("B13:Q19").Cells
cellref = c.Address
Workbooks("DH_Submission_Tool.xls").Worksheets("Q19").Activate
Range(cellref).Select
ActiveCell = ActiveCell.Value + c
Next

Thanks anyway

http://www.mattandhayley.me.uk

"Google is god...of the internet" - Me

 

[PHV]

Seems you haven't read my post dated 5 Sep 05 10:35

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Shippwreck]

I have read that post and replied to in post 5 Sep 05 11:04 at the top.

As i understand the code provided, it copies and pastes and therefore when i come to the next sheet it will just overwrite figures there and not add to existing ones. Now i haven't tested that code as mine now works, but it seems that you haven't fully read/understood my last post and if i am right then it would appear you haven't fully read/understood any of my posts.

http://www.mattandhayley.me.uk

"Google is god...of the internet" - Me

 

[PHV]

OK, have a look at the PasteSpecial method of the Range object.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886