Merging Data from many workbooks in to one workbook

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

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

---

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

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.

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

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

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))

---

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

Hi Experts, awaiting for your kind assistance.

Thanks
Rajesh

Working on this now

Try this in place of my last code

Code:

---

    Application.DisplayAlerts = False
    Do While ThisWorkbook.Worksheets.Count > 2
        ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count).Delete
    Loop
    Application.DisplayAlerts = True
    Set BaseWks = ThisWorkbook.Worksheets(2)

---

Hi, -

This seems woking fine. However the file i am copying contains password (same password for all files), and this code gives me an debug option about the protected sheets. and highlights .value=.value
Where shoud i define the password in this code to resolve this.

Write

Code:

---

.parent.unprotect "passwordhere"

---

right before that line

Hi in the sheet to which their is a link there is a sheet which does exaclty that, Each teacher makes a copy punch his data in and than any one sheet is used to import all of the other data where ever it has been saved usually it is done from a USB stick

Hi,

Can you please mention the link only to the file. There are several links in your post.

Thanks
Rajesh