Synchrinizing excel file

[teodormircea]

Hello everyone

Here a little project

I have 2 <> excel spreadsheets from <> workbooks , that i want to synchronize them .
The number of columns for both file are the same, but the number of lines are different.
The 4th column of each file contain the key match for every line.
So from the source file, i want to do a match with target file by the keys from the 4th columns and pull from the source all the in formation in the target file, keeping the information in the same column.

[mdmackillop]

Can you provide a sample?

[teodormircea]

Here by userform with explanation what i want to do

[mdmackillop]

You should state your requirements here. A sample workbook should contain layout, data types, text samples etc. Rephrasing your question in a workbook is not a "sample".

[teodormircea]

here by the samples and file with the code
explanation in filecode vba

[teodormircea]

Tell me if you need more information or explanation

[mdmackillop]

Option Explicit
Sub CopyData()
Dim wsSource As Worksheet
Dim wsTarget As Worksheet
Dim Field As String
Dim Rng As Range, cel As Range, c As Range
Dim Headings As Range, Hd As String
Dim Tgt As Long, Rw As Long, i As Long

Set wsSource = Workbooks("Source.xls").Sheets("A")
Set wsTarget = Workbooks("Target.xls").Sheets("DATA5")
Set Headings = wsTarget.Rows(1)

With wsTarget
Set Rng = Range(.Cells(2, 18), .Cells(Rows.Count, 18).End(xlUp))
For Each cel In Rng
With wsSource
Set c = .Cells.Find(cel)
If Not c Is Nothing Then
Rw = c.Row
For i = 1 To 12
Hd = .Cells(1, i)
Tgt = Application.Match(Hd, Headings, 0)
wsTarget.Cells(cel.Row, Tgt) = .Cells(Rw, i)
Next
End If
End With
Next
End With
End Sub

[teodormircea]

Thanks for your response, but here clear explained what i want to do.
I want to compare 2 different worksheets form 2 different workbook and then update the data in the second worksheet.
Both worksheets have different number of columns and lines.
So i was thinking to do that like the way vlookup is working.

FIRST.I have to choose the sheets i want to compare, by clicking on a cell from each sheet
SECOND.chose from each sheets a column that will be use to match the common values by clicking on a cell in first column and then in second column.
THIRD: Choose from the first sheet which data i want to move, so select the columns from the first sheets also by clicking on a cell of these columns.
4TH:select in the second sheet in what columns i want to update this data also clicking on the cell from each column

When the macro is lunched it will compare the values from the second step between the columns of the first and second sheet.For each common value found, the macros will pull out information from the chosen columns in the first sheet and update in the chosen columns in the second sheet.

[mdmackillop]

FIRST.I have to choose the sheets i want to compare, by clicking on a cell from each sheet
SECOND.chose from each sheets a column that will be use to match the common values by clicking on a cell in first column and then in second column.
THIRD: Choose from the first sheet which data i want to move, so select the columns from the first sheets also by clicking on a cell of these columns.
4TH:select in the second sheet in what columns i want to update this data also clicking on the cell from each column

I don't believe this is possible as stated. The closest I can suggest is to create a userform with RefEdit boxes to get the sheet/cell details

[teodormircea]

Like this one
See Attached file
I've design the userform but i don'k now to make the code.

[teodormircea]

What do you think about the user form??

[teodormircea]

Here i made the firs step.OPen workbooks from an file.
Now how to Select the sheets from first Workbook and the second Workbook

[teodormircea]

I'have a new version but with an eror, this time i select ranges from both sheets so that i can choose the column to use for compare but i'm stuck

[mdmackillop]

Use Option Explicit and fix your variables first, also, use

Userform1.Show False

which makes your fom non-modal.

[teodormircea]

Here a little more advanced.:dunno

[teodormircea]

Here at last a functional version, but i need some help for a new option.Is o make a complete matching, i mean not to choose the number of strings that must have in common.
I've added a new function.I just need an iddea how to match them