Consolidate Data From two different workbook into one

[fernandezblikz]

Hi Everyone,

I have two receivables data workbook and i want to consolidate it in one workbook. anyone can help me on this? Thank you

 

[My Aswer Is This]

Try this:

Both Workbooks must be open

In my script you will have two lines of code marked in red.
You need to modify these Workbook names to your needs
We have Workbook now which is your current workbook
We have Workbook Combined which will be the workbook where all sheets are copied to.
You need to modify the workbook names to your needs including extensions like "Sam.xls"

Sub Copy_Sheet_To_Other_Workbook()
Application.ScreenUpdating = False
Dim WBNow As String
Dim WBCombined As String
Dim i As Integer
Dim ans As Long
WBNow = "[COLOR=#ff0000]Book1[/COLOR]" 'Modify this workbook name to your liking
WBCombined = "[COLOR=#ff0000]Book3[/COLOR]" 'Modify this workbook name to your liking
ans = Workbooks(WBNow).Sheets.Count
Workbooks(WBNow).Activate
    For i = 1 To ans
        Workbooks(WBNow).Sheets(i).Copy After:=Workbooks(WBCombined).Sheets(Workbooks(WBCombined).Sheets.Count)
    Next
Application.ScreenUpdating = True
End Sub

 

[fernandezblikz]

Hi Sir,

Thanks it works but it make an additional sheet in current workbook. is it possible to consolidate it in one sheet in workbook? i really appreciate your help.

 

[My Aswer Is This]

Not sure I understand.
You have workbook named "Book1" wirh 10 sheets and you want all these sheets added to Workbook named "Book2"

That what my script does.

Are you now saying you want all the data in Book1 added to sheet1 of Book2 ?

Hi Sir,

Thanks it works but it make an additional sheet in current workbook. is it possible to consolidate it in one sheet in workbook? i really appreciate your help.

 

[My Aswer Is This]

Just using my previous post as an example.

In Post one you said:
I have two receivables data workbook and i want to consolidate it in one workbook. anyone can help me on this? Thank you

Maybe you should define consolidate. Consolidate into one workbook is what my script does. You did not say consolidate into one Worksheet.

 

[fernandezblikz]

Hi Sir,

Sorry if i didn't define it. Yes sir, is it possible to consolidate my data into one worksheet?

 

[My Aswer Is This]

I see your new to this Forum. And one thing is that we always need exact details.

In your post you gave no details.

We need.
The names of the two workbooks
The name of the Workbook you want the data copied to.
The name of the sheet you want all this data copied to.

And this could get complicated if in sheet(1) you have values only in column "G" But in sheet(3) you only have values in column "Z"

So we would need to know how these sheets are layed out.

This is why it's best in my opinion to not break out all your data into different Workbooks and different sheets.

Some people want to have a new workbook for each month of the year and a different worksheet for each week of the month.

So now when the boss says tell me how many apples we sold this year you have to go look in 12 different Workbooks and then look in 4 different worksheets in each Workbook. This can be a challenge.
OK maybe that's not what your doing so let me get off my "Soapbox".

Give us all the details and maybe we can help you.