matching 2 worksheets

  • Thread starter Thread starter Jerry
  • Start date Start date
J

Jerry

I have two long spreadsheets and i need to create a third worksheet with
information from both of them. Both of them have in common the employee
name, i want to match the name and the take some columns from worksheet 1 and
worksheet 2 and put all this information in worksheet 3, is it possible?
Thank you in advance.
 
Hi, yes, it could be done. I use this:

option explicit
sub Process()
'Creating the variables and asign them the sheets you use

dim mySheet1 as worksheet, mySheet2 as worksheet
dim mySheet3 as worksheet, i as long, j as long, k as integer

set mysheet1 = worksheets("Sheet 1")
set mysheet2 = worksheets("Sheet 2")
set mysheet3 = worksheets("Sheet 3")

i = 2
j = 2
'Going through your data and comparing cells
while mysheet2.cells(i,1) <> ""

'Compare the cells in the 2 worksheets
if mysheet2.cells(i,1) = mysheet1.cells(i,2) then

'Put the value of every column in sheet1 into sheet3
for k = 1 to 15
mysheet3 .cells(j,k) = mysheet1.cells(i,k)
next
j = j + 1

end if
i = i + 1

wend

If you have any questions, please let me know.
I hope this helps
 
I pasted the macro and tried running it but it does not move any data to the
third worksheet. HEre is the code that I have in the macro
Option Explicit
Sub merge_cells()

'Creating the variables and asign them the sheets you use

Dim mySheet1 As Worksheet, mySheet2 As Worksheet
Dim mySheet3 As Worksheet, i As Long, j As Long, k As Integer

Set mySheet1 = Worksheets("Sheet1")
Set mySheet2 = Worksheets("Sheet2")
Set mySheet3 = Worksheets("Sheet3")

i = 2
j = 2
'Going through your data and comparing cells
While mySheet2.Cells(i, 1) <> ""

'Compare the cells in the 2 worksheets
If mySheet2.Cells(i, 1) = mySheet1.Cells(i, 2) Then

'Put the value of every column in sheet1 into sheet3
For k = 1 To 15
mySheet3.Cells(j, k) = mySheet1.Cells(i, k)
Next

j = j + 1

End If
i = i + 1

Wend


End Sub
Can you tell me what am I doing wrong?
 
Back
Top