Macro to move the data across the sheets in a workbook after searching the matching ID

[Arunika]

Hi ,

I am very new to VBA Coding .I am trying to write a macro to move the data across the sheets in a workbook .
There are two sheets Active and Resignees. When I enter the ID in Resignees sheet (Cell D1 ) it should search the ID in Active sheet (Sheet having complete headcount details ) and return me with certain columns only .

Please help me with the correction for the below code .

I have tried a code :

Sub Button38_Click()


Dim i As Long


With ThisWorkbook.Worksheets("Resigned Employees")

If (ThisWorkbook.Worksheets("Resigned Employees").Range("D1").Value = ThisWorkbook.Worksheets("Active Employee").Cells(i, "A")) Then
For i = 2 To ThisWorkbook.Worksheets("Active Employee").Cells(.Rows.Count, "A").End(xlUp).Row




If Not IsEmpty(.Cells(i, "A").Value) Then

ThisWorkbook.Worksheets("Resigned Employees").Cells(i, "A").Value = ThisWorkbook.Worksheets("Active Employee").Cells(i, "A").Value
ThisWorkbook.Worksheets("Resigned Employees").Cells(i, "B").Value = ThisWorkbook.Worksheets("Active Employee").Cells(i, "B").Value
ThisWorkbook.Worksheets("Resigned Employees").Cells(i, "C").Value = ThisWorkbook.Worksheets("Active Employee").Cells(i, "C").Value
ThisWorkbook.Worksheets("Resigned Employees").Cells(i, "D").Value = ThisWorkbook.Worksheets("Active Employee").Cells(i, "D").Value
ThisWorkbook.Worksheets("Resigned Employees").Cells(i, "E").Value = ThisWorkbook.Worksheets("Active Employee").Cells(i, "H").Value
ThisWorkbook.Worksheets("Resigned Employees").Cells(i, "F").Value = ThisWorkbook.Worksheets("Active Employee").Cells(i, "AG").Value
ThisWorkbook.Worksheets("Resigned Employees").Cells(i, "G").Value = ThisWorkbook.Worksheets("Active Employee").Cells(i, "AI").Value
ThisWorkbook.Worksheets("Resigned Employees").Cells(i, "H").Value = ThisWorkbook.Worksheets("Active Employee").Cells(i, "AJ").Value
ThisWorkbook.Worksheets("Resigned Employees").Cells(i, "I").Value = ThisWorkbook.Worksheets("Active Employee").Cells(i, "AK").Value
ThisWorkbook.Worksheets("Resigned Employees").Cells(i, "J").Value = ThisWorkbook.Worksheets("Active Employee").Cells(i, "AL").Value
ThisWorkbook.Worksheets("Resigned Employees").Cells(i, "K").Value = ThisWorkbook.Worksheets("Active Employee").Cells(i, "AM").Value
ThisWorkbook.Worksheets("Resigned Employees").Cells(i, "L").Value = ThisWorkbook.Worksheets("Active Employee").Cells(i, "AN").Value
ThisWorkbook.Worksheets("Resigned Employees").Cells(i, "M").Value = ThisWorkbook.Worksheets("Active Employee").Cells(i, "AO").Value
ThisWorkbook.Worksheets("Resigned Employees").Cells(i, "N").Value = ThisWorkbook.Worksheets("Active Employee").Cells(i, "BH").Value

Else
MsgBox "Not Found!"

End If
End If


Next i

End With
MsgBox "Entry Completed"
End Sub






Thanks in Advance .

 

[JLGWhiz]

See if this will work for you.

Sub Button38_Click()
 Dim rng As Variant, fn As Range, sh1 As Worksheet, sh2 As Worksheet, r As Long
 Set sh1 = Sheets("Resigned Employees")
 Set sh2 = Sheets("Active Employee")
 With sh1
    Set fn = sh2.Range("A:A").Find(sh1.Range("D1").Value, , xlValues, xlWhole)
        If Not fn Is Nothing Then
            r = fn.Row
            With sh2
                rng = Array(.Cells(r, "A").Value, .Cells(r, "B").Value, .Cells(r, "C").Value, .Cells(r, "D").Value, _
                .Cells(r, "H").Value, .Cells(r, "AG").Value, .Cells(r, "AI").Value, .Cells(r, "AJ").Value, _
                .Cells(r, "AK").Value, .Cells(r, "AL").Value, .Cells(r, "AM").Value, .Cells(r, "AN").Value, _
                .Cells(r, "AO").Value, .Cells(r, "BH").Value)
            End With
            .Cells(Rows.Count, 1).End(xlUp)(2).Resize(1, 14) = rng
        Else
            MsgBox .Cells(1, 4).Value & " Not Found!", vbInformation, "NO MATCH"
        End If
 End With
 End Sub

 

[Arunika]

@jlgwhiz : It works like a charm .Thanks Alot .However can you help me to understand what does this code line mean ?

.Cells(Rows.Count, 1).End(xlUp)(2).Resize(1, 14) = rng

 

[JLGWhiz]

@jlgwhiz : It works like a charm .Thanks Alot .However can you help me to understand what does this code line mean ?

.Cells(Rows.Count, 1).End(xlUp)(2).Resize(1, 14) = rng

The full statement includes the With sh1 so it would be

sh1.Cells(Rows.Count, 1).End(xlUp)(2).Resize(1, 14) = rng

The rng variable is the prviously defined array consisting of 14 elements. The statement finds the last row with data starting at the bottom and working upward in column A, then offsets it by one row to the next available rofw. It then resizes the destination range to the 14 cells needed to receive the array which contains your source values in the rng variable.

 

[Arunika]

I was trying the same code for other sheets as well .What part of the code changes when we want to use it in other sheets ?
It is throwing me a error Type mismatch now when I changed the array size and new set of column values inside the array.

Sub Exit_Click()


Dim rng As Variant, fn As Range, sh1 As Worksheet, sh2 As Worksheet, r As Long
Set sh1 = Sheets("Exit ")
Set sh2 = Sheets("Active Employee")
With sh1
Set fn = sh2.Range("A:A").Find(sh1.Range("D1").Value, , xlValues, xlWhole)
If Not fn Is Nothing Then
r = fn.Row
With sh2
rng = Array(.Cells(r, "A").Value, .Cells(r, "B").Value, .Cells(r, "C").Value, .Cells(r, "D").Value, _
.Cells(r, "E").Value, .Cells(r, "F").Value, .Cells(r, "G").Value, .Cells(r, "H").Value, _
.Cells(r, "I").Value, .Cells(r, "J").Value, .Cells(r, "K").Value, .Cells(r, "L").Value, _
.Cells(r, "M").Value, .Cells(r, "N").Value, .Cells(r, "O").Value, .Cells(r, "P").Value, _
.Cells(r, "Q").Value, .Cells(r, "R").Value, .Cells(r, "S").Value, .Cells(r, "T").Value, _
.Cells(r, "U").Value, .Cells(r, "V").Value, .Cells(r, "W").Value, .Cells(r, "X").Value, _
.Cells(r, "Y").Value, .Cells(r, "Z").Value, .Cells(r, "AA").Value, .Cells(r, "AB").Value, _
.Cells(r, "AC").Value, .Cells(r, "AD").Value, .Cells(r, "AE").Value, .Cells(r, "AF").Value, _
.Cells(r, "AG").Value, .Cells(r, "AH").Value, .Cells(r, "AI").Value, .Cells(r, "AJ").Value, _
.Cells(r, "AK").Value, .Cells(r, "AL").Value, .Cells(r, "AM").Value, .Cells(r, "AN").Value, _
.Cells(r, "A0").Value, .Cells(r, "AP").Value, .Cells(r, "AQ").Value, .Cells(r, "AR").Value, _
.Cells(r, "AS").Value, .Cells(r, "AT").Value, .Cells(r, "AU").Value, .Cells(r, "AV").Value, _
.Cells(r, "AW").Value, .Cells(r, "AX").Value, .Cells(r, "AY").Value, .Cells(r, "AZ").Value, _
.Cells(r, "BA").Value, .Cells(r, "BB").Value, .Cells(r, "BC").Value, .Cells(r, "BD").Value, _
.Cells(r, "BE").Value, .Cells(r, "BF").Value, .Cells(r, "BG").Value, .Cells(r, "BH").Value, _
.Cells(r, "BI").Value, .Cells(r, "BJ").Value)


End With
.Cells(Rows.Count, 1).End(xlUp)(2).Resize(1, 62) = rng
Else
MsgBox .Cells(1, 4).Value & " Not Found!", vbInformation, "NO MATCH"
End If
End With
End Sub

 

[JLGWhiz]

.Cells(r, "A0").Value, .Cells(r, "AP").Value, .Cells(r, "AQ").Value, .Cells(r, "AR").Value, _

Change A0 to AO.

 

[Arunika]

It Works great !

Is it possible to pick values from two sheets simultaneously in the above code .We now pick values from sh2 .

I want to simultaneously pick from sheet 3 (Resigned Employee) also to put values into exit sheet (sh1).

Please help here !

Thanks

 

[JLGWhiz]

It Works great !

Is it possible to pick values from two sheets simultaneously in the above code .We now pick values from sh2 .

I want to simultaneously pick from sheet 3 (Resigned Employee) also to put values into exit sheet (sh1).

Please help here !

Thanks

You could make a separate array for the other sheet values, give it a different variable name and add a separate line of code to post the array values. Just follow the same principles used in the code above to declare the variables (Dim statements), initialize the array variable and post the results. If you are using the same cell references you could use a For loop instead of re-writing the entire array again for a separate sheet.

 

[Arunika]

Till BJ I need the data from active sheet .From BK I want it from resignees sheet. But it throws in an extra row below the actual row in exit sheet



Sub Exit_Click()


Dim rng As Variant, fn As Range, sh1 As Worksheet, sh2 As Worksheet, r As Long
Dim rng2 As Variant

Set sh1 = Sheets("Exit ")
Set sh2 = Sheets("Active Employee")
Set sh3 = Sheets("Resigned Employees")

With sh1
Set fn = sh2.Range("A:A").Find(sh1.Range("D1").Value, , xlValues, xlWhole)

If Not fn Is Nothing Then
r = fn.Row
With sh2
rng = Array(.Cells(r, "A").Value, .Cells(r, "B").Value, .Cells(r, "C").Value, .Cells(r, "D").Value, _
.Cells(r, "E").Value, .Cells(r, "F").Value, .Cells(r, "G").Value, .Cells(r, "H").Value, _
.Cells(r, "I").Value, .Cells(r, "J").Value, .Cells(r, "K").Value, .Cells(r, "L").Value, _
.Cells(r, "M").Value, .Cells(r, "N").Value, .Cells(r, "O").Value, .Cells(r, "P").Value, _
.Cells(r, "Q").Value, .Cells(r, "R").Value, .Cells(r, "S").Value, .Cells(r, "T").Value, _
.Cells(r, "U").Value, .Cells(r, "V").Value, .Cells(r, "W").Value, .Cells(r, "X").Value, _
.Cells(r, "Y").Value, .Cells(r, "Z").Value, .Cells(r, "AA").Value, .Cells(r, "AB").Value, _
.Cells(r, "AC").Value, .Cells(r, "AD").Value, .Cells(r, "AE").Value, .Cells(r, "AF").Value, _
.Cells(r, "AG").Value, .Cells(r, "AH").Value, .Cells(r, "AI").Value, .Cells(r, "AJ").Value, _
.Cells(r, "AK").Value, .Cells(r, "AL").Value, .Cells(r, "AM").Value, .Cells(r, "AN").Value, _
.Cells(r, "AO").Value, .Cells(r, "AP").Value, .Cells(r, "AQ").Value, .Cells(r, "AR").Value, _
.Cells(r, "AS").Value, .Cells(r, "AT").Value, .Cells(r, "AU").Value, .Cells(r, "AV").Value, _
.Cells(r, "AW").Value, .Cells(r, "AX").Value, .Cells(r, "AY").Value, .Cells(r, "AZ").Value, _
.Cells(r, "BA").Value, .Cells(r, "BB").Value, .Cells(r, "BC").Value, .Cells(r, "BD").Value, _
.Cells(r, "BE").Value, .Cells(r, "BF").Value, .Cells(r, "BG").Value, .Cells(r, "BH").Value, _
.Cells(r, "BI").Value, .Cells(r, "BJ").Value)

End With
.Cells(Rows.Count, 1).End(xlUp)(2).Resize(1, 63) = rng

Set fn = sh3.Range("A:A").Find(sh1.Range("D1").Value, , xlValues, xlWhole)
With sh3
rng2 = Array(.Cells(r, "O").Value, .Cells(r, "P").Value, .Cells(r, "R").Value, .Cells(r, "S").Value)
End With
.Cells(Rows.Count, 1).End(xlUp)(2).Resize(1, 4) = rng2


Else
MsgBox .Cells(1, 4).Value & " Not Found!", vbInformation, "NO MATCH"
End If


End With
End Sub

 

[JLGWhiz]

Till BJ  I need the data from active sheet .From BK I want it from resignees sheet. But it throws in an extra row below the actual row in exit sheet :(

If you are saying you want the results of both arrays to post on the same row then change your rng2 post as follows.

.Cells(Rows.Count, 1).End(xlUp).Offset(, 14).Resize(1, 4) = rng2