Get values from closed workbooks - range issue

[tsutho34]

Hi guys, I'm new to your boards and pretty new to VBA. But I have found a lot of useful information from this site in the past and now I need your help!!

I am using VBA to get data from a series of closed workbooks. My code works perfectly for one value, but I need a range of values. Can I please get advice on how to extend my range?

Background:
Each of the closed files have identical layout
Column A is labels
Title is in B2
Data is in B3:B17

The master file ("VBA test"):
Row 1 is labels
Column A is unique identifier for each file
Row 2, 3, 4 etc. is where I want data (transposed from original set)

Does that make sense???
please let me know If i can give you any further information.
(sadly I am at work and cannot upload files.)

Here is my code so far for the first value in each file. What i need is all 15 values from each file across each row.
Your help is MUCH appreciated!!!!

Please Login or Register  to view this content.

[patel45]

Please Login or Register  to view this content.

[tsutho34]

Thanks Patel45
I get a Compile error: "Method or data member not found" and (WS2.ActiveCell) is highlighted.
Can you help???

[FDibbins]

this could be done without using VBA. try using indirect()

=INDIRECT("'["&B$1&".xls]sheet1'!A"&ROW(A4))
where B1 contains the file name
sheet1'!A is the sheet name and column "number"
row(A4) is the row number you want to start at

once you have adjusted the references to suite your needs, this can be copied across and down

[tsutho34]

Thanks FDibbins, but I have about 150 spreadsheets and will be replicating this for about 20 tabs in each... so VBA I think would be my best bet.
Thanks for the suggestion though.
Do you have any ideas on the error I got from Patel45's code?

[patel45]

Please Login or Register  to view this content.

[jindon]

Do you need to open each file?

Please Login or Register  to view this content.

[tsutho34]

Oh THANK YOU jindon!!!!!!! That works so fast and perfectly!!!
Thank you, thank you, thank you!!!!!

Patel45 thank you as well for you help too!!

[tsutho34]

Another question on top of this one - but related.

I just found that one of my tabs has a second column of results....
How do I transpose column C so that is sits after the result from B17 on the same row?

in other words, the results in the master sheet will be in one row as before, but this time the results will show up as:
B2, B3, B4, ..., B16, B17, C2, C3, C4, ..., C17 then loop to the next row for the next workbook's data???

[jindon]

Is it like

Please Login or Register  to view this content.

?

[tsutho34]

That's exactly what I meant!! Thank you again SO much!!!

[tsutho34]

I know what your thinking... same issues again... but could use some more help on the above!

I have reused this code for different arrays on my worksheets, but for some sheets, I get all null values as a result. Can anyone explain what I have done wrong here????

[jindon]

The code I posted was written specifically based on your original conditions.

If the condition changed, you need to explain in detail.

[tsutho34]

Theoretically the conditions are the same as the first code you wrote for me, except this time the tab is called "B 26" and the cells are "F11:F27"
The code I have is;

Please Login or Register  to view this content.

But instead of getting the values from the closed sheets, all I get is "0" in every cell.

For some reason the vba works for some tabs but not others.....
Will work for tab "B 04" but not "B 05", will work for tab "B 18" but not "B 19"....???

[jindon]

If the formula returns 0, it means the origin value is either 0 or no value...

Did you check it?

[tsutho34]

Yeah I did and there is data in the cells. So I am unsure of what is wrong.
Like I said, it works on some tabs but not others....

The closed workbooks are 'read only' as of this morning. Would that affect anything?

[tsutho34]

Side note:
If I remove the loop and just test the first workbook I get null values also.
BUT if i open the workbook and re-run the macro, I get values....

[jindon]

Hummm, no idea.
This opens each file(not tested)
See if this works

Please Login or Register  to view this content.

[tsutho34]

Yeah that seems to work, all be it a little slowly! thanks again!!

How would I add other references to it? So I only have to open each workbook once....

eg. if I also wanted B19 and B35 from sheet "B 25" and K43:K45 from sheet "B 16", all following after??

[jindon]

Try change to

Please Login or Register  to view this content.

Can you upload the sheet(s) that the original code doesn't pick up the data?
I just want to find out what was the cause....

[tsutho34]

I'll try it now.
Code works GREAT!!! thank you ever so much once again!!!!


Sorry no I can't, I'm at work and the system does not allow for uploading. I also don't have the capacity at this stage to de-identify the data. But I appreciate your interest!!

[jindon]

OK, the first priority is to solve your problem and it is done.

If you ever get a chance to upload the questioned sheet(s), it will help me to find out the reason for my knowledge.