Merging Data from many workbooks in to one workbook

**Rajesh Kr Joshi** · 09-18-2011, 06:38 PM

Hi,

I got a macro code to merge data from diffrent files into one workbook. Currently when we run the code every time it make a new workbook and fetch the files basis given path. I wants, instead of making a new file it should update an existing file (I will assigne the macro to a button), and it should start merging the data from sheet2, because sheet1 is reserve for my dashboard.

Attached is the file with code.
Code source: http://www.rondebruin.nl/fso.htm

Thanks
Rajesh

**Excel Fox** · 09-18-2011, 07:31 PM

Search for the code

Code:

Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1)

with

Code:

    If ThisWorkbook.Worksheets.Count > 1 Then
        Set BaseWks = ThisWorkbook.Worksheets(2)
    Else
        Set BaseWks = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
    End If

**Rajesh Kr Joshi** · 09-18-2011, 10:08 PM

Hi, Implemented the below solution, while running the code getting dbug screen and highlighting the below line:

Code:

If Not sh Is Nothing Then
                sh.Copy After:=BaseWks.Parent.Sheets(BaseWks.Parent.Sheets.Count)

seems needs to make changes to this line as well.

Thanks
Rajesh

**Excel Fox** · 09-18-2011, 10:30 PM

can you post the error description?

Is it by any chance, this..

Code:

Excel cannot insert the sheets into the destination workbook, because it contains fewer rows and columns than the source workbook. To move or copy the data to the destination workbook, you can select the data, and then use the Copy and Paste commands to insert it into the sheets of another workbook.

If yes, try using the attached Excel 2007-2010 file.

**Rajesh Kr Joshi** · 09-18-2011, 11:09 PM

This code is working fine

. Let me implement this in the real sheet that i am working on. Will update the results.

Thanks for the help.
Rajesh

**Rajesh Kr Joshi** · 09-19-2011, 11:15 PM

Hi, This code keeps adding sheets (makes dupilcate) , if i run it for more then one time. Is it possible to over write the exixting sheets. My scenario is - I have the source workbooks with unique names and i wants to update the destination file (the file with code) every time i run the code. On more thing while fetching the data if there is an excel 2003 file it only the name in the sheet same as the source file name , but if the file is a excel 2007, it shows sourcefilename.xlsx.

Thanks
Rajesh

**Excel Fox** · 09-20-2011, 09:16 AM

Replace my last piece of code with

Code:

    Application.DisplayAlerts = False
    Do While ThisWorkbook.Worksheets.Count > 1
        ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count).Delete
    Loop
    Application.DisplayAlerts = True
    Set BaseWks = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))

**Rajesh Kr Joshi** · 09-20-2011, 05:18 PM

Hi, I replaced the code, when you run the code it seems it is doing somthing ,but it is dosent add the data, it shows only one blank sheet.
Is it possible fot you to implement in the file that i attached and send.

Thanks
Rajesh