How To Consolidate Data From Different Sheets to One Summary Sheet

Originally Posted by Leith Ross

Hello Lawliet918,

Welcome to the Forum!

I haven't opened the zip file yet. Do you need a solution using Excel formulas, VBA macros, or either?

Hi Leith,

Thanks for the response. I'm trying to learn VBA Macros so I'd appreciate it if the solution' through VBA macros.

Originally Posted by oeldere

You better could change the title.

You want to make a consolidated file, from differant workbooks (instead of worksheets).

1 workbooks contains several worksheets.

hi,

you're right. sorry, my bad. will update the title.

Originally Posted by oeldere

You better could change the title.

You want to make a consolidated file, from differant workbooks (instead of worksheets).

1 workbooks contains several worksheets.

hi,

would you know how to edit the title? i'm trying to look for the settings but couldn't seem to find it.

thanks!

Originally Posted by oeldere

I believe it is above your first question #1, in the toolbar

that one only edits the post. but not the title. anyway, i'll edit the post to mean they're from different workbooks.

Originally Posted by Leith Ross

Hello Lawliet918,

In your Summary are you summing only the "Cash" entry from each branch?

Do you want to create summaries for the other ledger accounts A/R, A/P, etc. for each branch also?

Hi Leith,

I just made Cash as an example. But I would definitely need to have separate worksheets for the other ledger accounts. So, in theory, I should have one ledger account summary per worksheet.

Originally Posted by Leith Ross

Hello Lawliet918,

In your Summary are you summing only the "Cash" entry from each branch?

Do you want to create summaries for the other ledger accounts A/R, A/P, etc. for each branch also?

please note that the account summary worksheet should still reflect the account balance per branch, and not a sum of the account balances of all branches.

Originally Posted by Leith Ross

Hello Lawliet918,

Okay, I think I have this working the way you want. Here is what the macro does.

1. It looks for the branch files in the same folder where the Summary workbook is saved.
   
2. Each workbook in the folder that has an xls, xlsx, or xlsm extension will opened. The workbook name less the extension is used as the branch name.
   
3. "Sheet1" is examined starting at cell "A1". Each cell in the range starting at "A1" to the last non empty cell in column "A" is used as the worksheet name in the Summary workbook. If the worksheet by this name does not exist then it will be created using the "Template" worksheet in the Summary workbook. This sheet is hidden. The branch is added to the next empty row of the Summary worksheet along with the cash value. this process continues until all workbooks have been examined and copied.

Summary Macro Code

Please Login or Register  to view this content.

hi leith,

thanks for the script.

however, i ran the code and i'm getting run-time error '9': subscript out of range.

would you know what's causing this?

Originally Posted by Leith Ross

Hello Lawliet918,

There could be a variety of reasons for this error occurring. I made the code more robust and able to handle the more common reasons for this error.

Jere is the modified macro code. The attached workbook has the changes.

[b\Update Macro Code[/b]

Please Login or Register  to view this content.

Okay. So I tried to see if I got some basic coding done right and so I did the subroutine below for Cash.

It's kind of working as it copies the value of the account to a summary worksheet (i.e. BranchSummary.xlsx) but what I'm missing is how to run this subroutine automatically for like 20 workbooks each representing a distinct branch. Also, I need to have another column to indicate which branch did this account balance come from. I'm attaching four files to hopefully get you a better idea of what I want to do:

The files are:

1. Branch 1.xlsm - raw file for Branch 1
2. Branch 2.xlsm - raw file for Branch 2
3. BrandMaster.xlsx - what it looked like after running the subroutine below for both Branches
4. BrandMasterShouldBe.xlsx - what it should look like and what I really wanted the format to be

And here's the code I tried to write:

*******

Sub Cash()

Dim LastRow As Integer, i As Integer, erow As Integer

LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row

For i = 1 To LastRow

If Cells(i, 1) = "Cash" Then
Range(Cells(i, 1), Cells(i, 2)).Select
Selection.Copy

Workbooks.Open Filename:="C:\Users\lsoriano006\Desktop\RIL_TEST\New folder\BrandMaster"
Worksheets("Sheet1").Select
erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

ActiveSheet.Cells(erow, 1).Select
ActiveSheet.Paste
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.CutCopyMode = False
End If

Next i
End SubFor Data Consolidation_UPDATED.zip

Originally Posted by Leith Ross

Hello Lawliet918,

There could be a variety of reasons for this error occurring. I made the code more robust and able to handle the more common reasons for this error.

Jere is the modified macro code. The attached workbook has the changes.

[b\Update Macro Code[/b]

Please Login or Register  to view this content.

Hi Leith,

Just to give you a more realistic format of the reports per branch, I'm attaching the following:

Note that the Cash Account Summary is just an example. I may have another Excel workbook for other significant accounts, say Accounts Receivable. But a subroutine for Cash should be able to provide precedent to other account subroutines.

Thanks again!

For Macro.zip