Possible to write codes that can combine several workbooks of different formulas?

[celia2r]

Hi All,

I'm very new to VBA and I have a question

I have several workbooks of same formats. Each workbook contains two worksheets, one is the data set, one is the outcomes from the other one.

Worksheet 1 is like this:
A(name) B(class) C(type) D E
1 xxx x xxx # #
2
3
4
5

D and E are calculated from the data of Worksheet 2, but each of the workbooks is using a different formula.

The workbooks are classified based on different B and Cs (like certain Bs and Cs belongs to Workbook 1, others belongs to Workbook 2).

I'm just wondering is it even possible to use VBA to combine these workbooks into one so that it can identify the ones that have same B and C, then apply its corresponding formula?

I know it might be confusing, but I really need some help! Any ideas or comments?

Thank you in advance

[arlu1201]

Yeah i bet its confusing.

Why dont you upload a sample of each of your different files that way we can look at them and advice if they can be consolidated or not?

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

[celia2r]

Hi Arlette,

Thank you for reply! I've uploaded two sample files I have but the real ones are actually more complicated than these.

So basically, D and E (Crit and GHG) in sheet 1 of the workbooks are the information we need. They are calculated using the values from sheet 2. We wrote macros for these calculations. Book 1 and Book 2 are of the same format (and we have like 7 or 8 these workbooks in total). But each of them is using a different formula.

Both class and fuel (B and C) determines the type of the technology. And for each technology we use a different formula. We are currently using a seperate workbook for each of technology.

My questions is, is it possible to put these workbooks into one, that we can find certain items based on their B and C (class and fuel), and then let them do certain calculations?

If anyone has any ideas please let me know!

Thanks again...

[celia2r]

Desperate for assistance...Hope someone will take a look. To update I am posting the formulas I have for 2 of the workbooks. The values and number of rows in different workbooks are different. But all other things here (like those in sheets3) are the same. I've highlighted the difference between the formulas:

Workbook 1:
'Perform calculations for D
f1 = Sheets("Sheet2").Range("B8")
fb1 = Sheets("Sheet2").Range("C8")
j1 = Sheets("Sheet2").Range("D8")
d = (Weight - 228) * Sheets("Sheet3").Range("B8") * _
f1 / 10 + BWeight * Sheets("Sheet3").Range("B8") * _
fb1 / 10 + Sheets("Sheet3").Range("B8") * j1 / 10
'Perform calculations for E
c1 = Sheets("Sheet2").Range("B9") * Sheets("Sheet3").Range("C5")
c2 = Sheets("Sheet2").Range("B10") * Sheets("Sheet3").Range("C6")
c3 = Sheets("Sheet2").Range("B11") * Sheets("Sheet3").Range("C7")
cb1 = Sheets("Sheet2").Range("C9") * Sheets("Sheet3").Range("C5")
cb2 = Sheets("Sheet2").Range("C10") * Sheets("Sheet3").Range("C6")
cb3 = Sheets("Sheet2").Range("C11") * Sheets("Sheet3").Range("C7")
i1 = Sheets("Sheet2").Range("D9") * Sheets("Sheet3").Range("C5")
i2 = Sheets("Sheet2").Range("D10") * Sheets("Sheet3").Range("C6")
i3 = Sheets("Sheet2").Range("D11") * Sheets("Sheet3").Range("C7")
e = (Weight - 228) / 10 * (c1 + c2 + c3) + BWeight / 10 * _
(cb1 + cb2 + cb3) + (i1 + i2 + i3) / 10

Workbook 2:
'Perform calculations for D
f1 = Sheets("Sheet2").Range("B2")
j1 = Sheets("Sheet2").Range("C2")
d = (Weight - 228) * Sheets("Sheet3").Range("B8") * _
f1 / 10 + Sheets("Sheet3").Range("B8") * j1 / 10
'Perform calculations for E
c1 = Sheets("Sheet2").Range("B3") * Sheets("Sheet3").Range("C5")
c2 = Sheets("Sheet2").Range("B4") * Sheets("Sheet3").Range("C6")
c3 = Sheets("Sheet2").Range("B5") * Sheets("Sheet3").Range("C7")
i1 = Sheets("Sheet2").Range("C3") * Sheets("Sheet3").Range("C5")
i2 = Sheets("Sheet2").Range("C4") * Sheets("Sheet3").Range("C6")
i3 = Sheets("Sheet2").Range("C5") * Sheets("Sheet3").Range("C7")
e = (Weight - 228) / 10 * (c1 + c2 + c3) + (i1 + i2 + i3) / 10

[arlu1201]

Ok, this is complicated but it can be done. We will need to create a comprehensive macro which has all the formulae.

Do you want the formulae to be shown in the cells or is it ok if values show after the macro performs the calculations?

We can break this up into 2 sections -
1. Consolidation
2. Applying formulae

Which are the columns to be consolidated as per step1?

[celia2r]

Hi! Thanks for spending time to help I don't need the formula to be shown in the cells.

Actually what I was trying to do is not only to consolidate all the data together, but to develop a single workbook for our project this year, instead of 7 or 8 as we did in past. I was hoping to do that by making change on our codes last year.

Will it be a very complicated macro? Is it going to be easier to stay on the multiple workbooks to do these tasks?

Ok, this is complicated but it can be done. We will need to create a comprehensive macro which has all the formula.

Do you want the formulae to be shown in the cells or is it ok if values show after the macro performs the calculations?

We can break this up into 2 sections -
1. Consolidation
2. Applying formulae

Which are the columns to be consolidated as per step1?

[arlu1201]

You can have one workbook itself. It may take some time for the macro to run and do all the calculations.

So lets do this step by step -

1. Which are the columns to be consolidated?

[celia2r]

Okay, thanks! I want to have all the columns to be consolidated. I actually have much more columns and worksheets to be consolidated, but these as I put here are what matters.

You can have one workbook itself. It may take some time for the macro to run and do all the calculations.

So lets do this step by step -

1. Which are the columns to be consolidated?

[arlu1201]

It will be good if you tell me how many columns based on your original sheet. I am saying this to avoid confusion at a later stage. Your file is complicated and whatever steps we take to make it simple from the very beginning will be good for you and me .

Just tell me the last column containing data in your original file. Is it K or Z or whatever?

[celia2r]

The last one is CY...lol

And the ones I've picked up for sheet 1 are B, C, K, CM, and CP.

Thank you so much for being patience!

It will be good if you tell me how many columns based on your original sheet. I am saying this to avoid confusion at a later stage. Your file is complicated and whatever steps we take to make it simple from the very beginning will be good for you and me .

Just tell me the last column containing data in your original file. Is it K or Z or whatever?

[arlu1201]

(Do not quote whole posts, it just increases the length of the thread and clutters it up).

So along with B, C, K, CM, and CP, you need all the columns to be consolidated right? And not just those 5?

[celia2r]

Yes! I want all the columns to be consolidated.

(Do not quote whole posts, it just increases the length of the thread and clutters it up).

So along with B, C, K, CM, and CP, you need all the columns to be consolidated right? And not just those 5?

[arlu1201]

This is the code for the consolidation -

Please Login or Register  to view this content.

Put it in a standard module. Replace the bold text with your system path where the files are located. This macro will consolidate the data from columns A to CY from all the files as long as they are stored in 1 folder.

Then we move forward to the formula part once you have tested this part.

[celia2r]

Hi Arlette!

Thanks so much for help!

I tried to run this but didn't have much luck. When I hit "debug", this line

Workbooks.Open (myPath & "\" & CurrentFileName)

becomes yellow. It also said the path can not be found, however I'm pretty sure it is right.

Could you please help me to take a look?

Thank you.

[arlu1201]

Did you change the path "D:\Test" to your system path where the files are located?

[celia2r]

Yes I've changed that, and I'm pretty sure it's right! Is this the only thing I need to change? Sorry I'm so new to this and thank you so much for help!!

Did you change the path "D:\Test" to your system path where the files are located?

[arlu1201]

What is the path that you have changed in the code?

[celia2r]

I changed it like this:

'The folder containing the files to be recap'd
myPath = "C:\Documents and Settings\AC\Desktop\trials\New Folder"

What is the path that you have changed in the code?

[arlu1201]

Can you check the spelling of the path and even the case (upper or lower) matches your system exactly?

[celia2r]

Now I was able to consolidate the files, but it doesn't seem right. The format of the speadsheets is now changed.

1. Column A becomes empty, and there is an additional column B, with the name of the files. Column C becomes former Column A.

2. There's an additional empty row 1, and row 2 with no titles.

3. I have like 20 sheets on one workbook before, but now I only have sheet 1 on the consolidated one, all others disappeared.

I'm so desparate

But THANK YOU SO MUCH, Arlette!

Can you check the spelling of the path and even the case (upper or lower) matches your system exactly?

[arlu1201]

1. Column A becomes empty, and there is an additional column B, with the name of the files. Column C becomes former Column A.

My bad. Replace this code

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

2. There's an additional empty row 1, and row 2 with no titles.

Taken care of in the previous code for point 1.

3. I have like 20 sheets on one workbook before, but now I only have sheet 1 on the consolidated one, all others disappeared.

Which workbook are you talking about which has 20 sheets?

[celia2r]

Thanks All the workbooks I have have 20 sheets, 28 sheets actually. And I have 10 files to consolidate...

[arlu1201]

Oh, you didnt mention that before. So the 28 sheets should be consolidated into 1 master file? And then move to the next 28 sheets from the next file below the first file's data, etc?

[celia2r]

Hi! Maybe I didn't explain it clear enough... because my workbooks are so complicated. As in my sample, sheet 1 in the workbooks are calculated from the other sheets. And the reason why I'm using 10 workbooks in the past years is because they are using different formulaes. But only the two columns I mentioned are where the difference is.

So basically, I want to use one workbook this year (we'll receive new data) instead of using that many. So I was hoping to get one workbook with 28 sheets.

Oh, you didnt mention that before. So the 28 sheets should be consolidated into 1 master file? And then move to the next 28 sheets from the next file below the first file's data, etc?

[arlu1201]

Ok, so if i understand it clearly, you want 1 workbook with 28 sheets where sheet1 will contain data from sheet 1 of each of the 10 workbooks, sheet 2 will contain data from sheet 2 of each of the 10 workbooks, etc, right?

Are the sheets named the same in all the 10 workbooks, for e.g. if sheet1 in workbook1 is data, in workbook2, its also called data, etc ?

[celia2r]

Yes exactly! Sorry for the confusing message... They are all named the same.

I was hoping to use the new workbook to analyze this year's data. The 10 books are last year's result.

Do you think we need to combine the workbooks first or we can write macros to apply the formulaes directly?

Ok, so if i understand it clearly, you want 1 workbook with 28 sheets where sheet1 will contain data from sheet 1 of each of the 10 workbooks, sheet 2 will contain data from sheet 2 of each of the 10 workbooks, etc, right?

Are the sheets named the same in all the 10 workbooks, for e.g. if sheet1 in workbook1 is data, in workbook2, its also called data, etc ?

[arlu1201]

If you want to put in the formulae for last year's result, then you can put the formulae in directly.

But for the current year, since you want all the data in one file, you will need to consolidate it first and then apply the formulae.

[celia2r]

Thanks for being so patience So will it be very difficult to change the codes?

If you want to put in the formulae for last year's result, then you can put the formulae in directly.

But for the current year, since you want all the data in one file, you will need to consolidate it first and then apply the formulae.

[arlu1201]

No, i can work on it for you. So you want to first do the consolidation right?

[celia2r]

Yes. That would be a big help actually! I appreciate your help sooo much!

No, i can work on it for you. So you want to first do the consolidation right?