Inserting the data in to respective columns

Hi All,

I have an excel workbook with some common heading column. Now I need to insert all the data into the respective columns in a separate workbook.
Is there any VB Code or Macro to do so? Please find the attachment.

Once after inserting the data. The new workbook should contain both the common column like

ID Contact Designation Company Address

The above formula is not getting the result.

Is the intent to synchronize the two files? So that both would contain the following columns/data
ID Contact Designation Company Address
If so, it can be done with VBA code. It may not be easy, but it can be done. One complication would be trying to match on name

Yes I have to synchronize the two file.

There are some other questions:
- do you need to maintain 2 files or can 1 then be dropped?
- does the column order matter at all?
- if there is a discrepancy with data between the two files for the same person, which takes precedence?

Some VBA Code would be required, but it wouldn't be something simple.

1. Single file clubbed with both data.
2. Column ordering doesn't matter at all.
3. Whatever the data are there just merge in with the file1 in the respective column.

In access we can do it by INSERT query.

1. Single file clubbed with both data.
2. Column ordering doesn't matter at all.
3. Whatever the data are there just merge in with the file1 in the respective column.

As far as I know there is no native INSERT function, or anything similar to it, in Excel. You can simulate it somewhat if you want to do a lot of coding.
If this is a one time thing, it would be easier to do it manually.

I have many files like this to merge in to one file. Every time I will try to do it manually. I am looking for the formula or a VB code to do my task simpler.

Creating the code to this would take some time and would likely need some discussion to nail down the process.
If you have the experience with MS Access, why not use the Excel files as an external data source and then use the MS Access INSERT queries to build the database. If I remember correctly, you can then use the MS Access database as a source for Excel.

I have a vb code to merge the data according to the sheet. But it will not merge according to the column. Can you modify this code?

Sub GetSheets()
Path = "C:\Users\sathish.STECHBLR\Desktop\"
Filename = Dir(Path & "*.xlsx")
Do While Filename <> ""
Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
For Each Sheet In ActiveWorkbook.Sheets
Sheet.Copy After:=ThisWorkbook.Sheets(1)
Next Sheet
Workbooks(Filename).Close
Filename = Dir()
Loop


Dim J As Integer
On Error Resume Next
Sheets(1).Select
Worksheets.Add
Sheets(1).Name = "Combined"

Sheets(2).Activate
Range("A1").EntireRow.Select
Selection.Copy Destination:=Sheets(1).Range("A1")

For J = 2 To Sheets.Count
Sheets(J).Activate
Range("A1").Select
Selection.CurrentRegion.Select

Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select

Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
Next


End Sub

I was able to get this done faster that I thought.
This code should do the trick. (I couldn't figure out how to add a file to a reply)
It worked when tested with your files but I can't guarantee that it will work with all the files you may have to combine. You may want to add some code to change formatting afterwards.
You'll have to adjust the path (sPath variable) in the code
If this is want you want, please click on the Add Reputation link at the bottom.

Please Login or Register  to view this content.

The code is not executing.

what is it not doing? You'll need to manually start it, unless you set up a button to start it off.
Did you change the value of sPath when you copied it into your file?
One other thing you may need to change. I was looking for .xlsx files. If you want .xls, or something else, you need to change this line

Please Login or Register  to view this content.

I am getting the error message say that " Cant execute code in break mode" in

Do While Len(Trim(Worksheets(sh_Src).Cells(nRow, nCol).Value)) > 0

It sounds like you're trying to execute a macro when one is already in break mode.
See this article http://support.microsoft.com/kb/177828

I have no issues running the code opening the file and running the macro.

Any solution about this query?