lookup from one sheet and save in other sheet

[ganeshm]

hi excel experts,

i used the following code to copy data from sheet 1 to sheet 2,

Sub Save()
Dim LastRow As Integer
LastRow = Worksheets("sheet2").Range("C65536").End(xlUp).Row
Sheets("sheet1").Range("H5").Copy Worksheets("sheet2").Cells(LastRow, 3)
With Worksheets("sheet1").Activate
Range("H5").Select
End With
End Sub

Notes:

1. i was trying to lookup cell A1 (sheet 1) and take the cell value of "H5".

2. The cell value of "H5" should be copied to 3rd row of sheet 2 matching the column A, where the candidates roll no. is mentioned.

e.g.

clm A clm C

101 cell value (matching A1 of sheet 1)

102

103

thank you,

Regards,
ganeshm

 

[Somendra Misra]

Hi Ganesh,

How about a sample file with some data.

Regards,

 

[ganeshm]

hi somendra,

i want use merge cell "AR25" instead of AZ (which i used for testing a sample data).

thanks

 

[Nebu]

Hi:

Your description in the first post is different from the file you had posted. I am not sure what you want to achieve here. With what I have understood from the macro you had written in the attached file, I made the following modifications.

Sub Save()

Dim LastRow As Integer

LastRow = Sheet1.Range("C" & Rows.Count).End(xlUp).Row + 1
Sheet1.Cells(LastRow, 2) = Sheet1.Cells(LastRow - 1, 2) + 1
Sheet1.Cells(LastRow, 4) = Sheet3.Range("AR25")

Sheet3.Activate
Range("G2").Select

End Sub

Thanks

 

[ganeshm]

hi...

pls ignore the previous attachment.

Kindly advise me where i am going wrong in the code. Because if i press save button. It is overwriting the value in the 2nd sheet. But i would want to save the value to the corresponding employee, not to overwrite.

thanks...

 

[Nebu]

Sub Save()

Dim LastRow As Integer

LastRow = Sheet1.Range("C" & Rows.Count).End(xlUp).Row
Sheet1.Cells(LastRow, 2) = Sheet1.Cells(LastRow - 1, 2) + 1
Sheet1.Cells(LastRow, 4) = Sheet3.[AZ25]
Sheet3.Activate

End Sub

I modified your code , It is same as the one I gave in my above post. If you have any additional requirement I am afraid you will have to explain a bit more.

Thanks

 

[ganeshm]

I tried but code is not matching.

Actually i am trying to lookup the emp no. and match the balance leave.

When i get the balance leave of that particular employee it needs to be saved in other sheet adjacent to that particular employee

 

[Nebu]

HI:

From your file I understand that the balance leave is in the cell AR25 and
you want this value to be saved in the next sheet in Column D corresponding to the employee ,the macro is doing the same. But I assume that your candidate names and badge nos are pre-populated in the Employee Leave Tracker sheet.


On the other hand if you want update it based on AK2 of your Calendar View sheet use the following code

Sub Save()

Dim LastRow As Integer
Dim rng As Range
Dim emp As String
emp = Sheet3.[AK2]
Set rng = Sheet1.Range("C:C").Find(What:=emp, Lookat:=xlWhole, MatchCase:=False)
LastRow = rng.Row
Sheet1.Cells(LastRow, 4) = Sheet3.[AZ25]
Sheet3.Activate

End Sub

Note in the above code change Sheet3.[AZ25] to Sheet3.[AR25] if you want the calculated leave balance

Thanks

 

[ganeshm]

it says run time error 92

LastRow = rng.Row

i want to save it in sheet1 (where i have badge no. employee and leave balance), not in the sheet where i have the calendar.


thanks in advance

 

[Nebu]

Hi:
Can you upload the workbook where you ran this macro. It did run at my end without any error.

Thanks

 

[ganeshm]

sure here is the sheet....

Please note, i am trying to lookup G2 (where badge no.= 101) not the name.

 

[Nebu]

Hi:


You did changed my code but, you should first understand the logic before changing it ,press the F8 key and go through the macro step-by-step to understand it more clearly. Here is the revised code

Sub Save()

Dim LastRow As Integer
Dim rng As Range
Dim Badge As String
Badge = Sheet3.[G2]
Set rng = Sheet1.Range("A:A").Find(What:=Badge, Lookat:=xlWhole, MatchCase:=True)
LastRow = rng.Row
Sheet1.Cells(LastRow, 3) = Sheet3.[AR25]
Sheet3.Activate

End Sub

Thanks

 

[ganeshm]

thank you...

and i am sorry... iam just learning the macro and codes now. Henceforth, i will read it and apply it carefully.

thanks again.

 

[ganeshm]

hi ...

how do i rename the sheet in macro... ?

Sheet3 as "Calendar View" and sheet1 as "Employee Leave".

thanks