Need to copy from sourcesheet to destination sheet only the new data entered based on serial no on the source sheet

Chitaah · May 18th 2021

I am totally new to Excel VBA. But I have a requirement to copy some data from let us say sheet1 to sheet2. I have several columns and rows in sheet1 (source sheet). Now I have to copy only Five columns data from within that where "Ferro Manganese" found in particular one coloum and if found then just copy the data's from the rows of the other 4 columns also and then paste in to the destination rows of the specified column there in Sheet2. Till this step I have done it successfully. Now the users are entering new data to the source sheet the next day and I only need to update those data to the destination sheet. The new data can be selected based on new Serial no on the sheet1 which is available in coloum "A". For every new entry they enter a increased serial number (Which is unique). so If I can check the "Ferro Manganese" condition from the newly entered serial number (from the point Serial no increased) then I can only copy those new data to the destination sheet using the successful code i am attaching it here. I need to learn how to write that extra if condition for increased serial no condition depending on which the paste function will work for only the new data. Due to office data populated in the sheet I could not upload it. But from the code the description I think you masters will understand it easily.

Please help me.

Code

Sub Datafetch()


Dim lastrow As Long, erow As Long


Dim LastRowIndex As Integer
Dim RowIndex As Integer
Dim UsedRng As Range




lastrow = Worksheets("SIPL Details").Cells(Rows.Count,
1).End(xlUp).Row
erow = Worksheets("Ferro Tracking").Cells(Rows.Count,
1).End(xlUp).Row




For i = 2 To lastrow


If Worksheets("SIPL Details").Cells(i, 7).Value =
"Ferro Manganese" Or Worksheets("SIPL Details").Cells(i,
7).Value = "Silico Manganese" Then


Worksheets("SIPL Details").Cells(i, 3).Copy
Worksheets("SIPL Details").Paste
Destination:=Worksheets("Ferro Tracking").Cells(erow + i, 2)
Worksheets("Ferro Tracking").Cells(erow + i, 1).Value
= "SIPL"
Worksheets("SIPL Details").Cells(i, 7).Copy
Worksheets("SIPL Details").Paste
Destination:=Worksheets("Ferro Tracking").Cells(erow + i, 4)
Worksheets("Ferro Tracking").Cells(erow + i, 1).Value
= "SIPL"
Worksheets("SIPL Details").Cells(i, 14).Copy
Worksheets("SIPL Details").Paste
Destination:=Worksheets("Ferro Tracking").Cells(erow + i, 7)
Worksheets("Ferro Tracking").Cells(erow + i, 1).Value
= "SIPL"
Worksheets("SIPL Details").Cells(i, 9).Copy
Worksheets("SIPL Details").Paste Destination:=Worksheets("Ferro
Tracking").Cells(erow + i, 11)
Worksheets("Ferro Tracking").Cells(erow + i, 1).Value
= "SIPL"
Worksheets("SIPL Details").Cells(i, 5).Copy
Worksheets("SIPL Details").Paste
Destination:=Worksheets("Ferro Tracking").Cells(erow + i, 13)
Worksheets("Ferro Tracking").Cells(erow + i, 1).Value
= "SIPL"






End If




Next i




For i = 2 To lastrow


If Worksheets("ESPL Details").Cells(i, 7).Value =
"Ferro Manganese" Or Worksheets("ESPL Details").Cells(i,
7).Value = "Silico Manganese" Then


Worksheets("ESPL Details").Cells(i, 3).Copy
Worksheets("ESPL Details").Paste
Destination:=Worksheets("Ferro Tracking").Cells(erow + i, 2)
Worksheets("Ferro Tracking").Cells(erow + i, 1).Value
= "ESPL"
Worksheets("ESPL Details").Cells(i, 7).Copy
Worksheets("ESPL Details").Paste
Destination:=Worksheets("Ferro Tracking").Cells(erow + i, 4)
Worksheets("Ferro Tracking").Cells(erow + i, 1).Value
= "ESPL"
Worksheets("ESPL Details").Cells(i, 14).Copy
Worksheets("ESPL Details").Paste
Destination:=Worksheets("Ferro Tracking").Cells(erow + i, 7)
Worksheets("Ferro Tracking").Cells(erow + i, 1).Value
= "ESPL"
Worksheets("ESPL Details").Cells(i, 9).Copy
Worksheets("ESPL Details").Paste Destination:=Worksheets("Ferro
Tracking").Cells(erow + i, 11)
Worksheets("Ferro Tracking").Cells(erow + i, 1).Value
= "ESPL"
Worksheets("ESPL Details").Cells(i, 5).Copy
Worksheets("ESPL Details").Paste
Destination:=Worksheets("Ferro Tracking").Cells(erow + i, 13)
Worksheets("Ferro Tracking").Cells(erow + i, 1).Value
= "ESPL"






End If




Next i




For i = 2 To lastrow


If Worksheets("BSIPL Details").Cells(i, 7).Value =
"Ferro Manganese" Or Worksheets("BSIPL Details").Cells(i,
7).Value = "Silico Manganese" Then


Worksheets("BSIPL Details").Cells(i, 3).Copy
Worksheets("BSIPL Details").Paste
Destination:=Worksheets("Ferro Tracking").Cells(erow + i, 2)
Worksheets("Ferro Tracking").Cells(erow + i, 1).Value
= "BSIPL"
Worksheets("BSIPL Details").Cells(i, 7).Copy
Worksheets("BSIPL Details").Paste
Destination:=Worksheets("Ferro Tracking").Cells(erow + i, 4)
Worksheets("Ferro Tracking").Cells(erow + i, 1).Value
= "BSIPL"
Worksheets("BSIPL Details").Cells(i, 14).Copy
Worksheets("BSIPL Details").Paste
Destination:=Worksheets("Ferro Tracking").Cells(erow + i, 7)
Worksheets("Ferro Tracking").Cells(erow + i, 1).Value
= "BSIPL"
Worksheets("BSIPL Details").Cells(i, 9).Copy
Worksheets("BSIPL Details").Paste
Destination:=Worksheets("Ferro Tracking").Cells(erow + i, 11)
Worksheets("Ferro Tracking").Cells(erow + i, 1).Value
= "BSIPL"
Worksheets("BSIPL Details").Cells(i, 5).Copy
Worksheets("BSIPL Details").Paste
Destination:=Worksheets("Ferro Tracking").Cells(erow + i, 13)
Worksheets("Ferro Tracking").Cells(erow + i, 1).Value
= "BSIPL"






End If




Next i








Set UsedRng = ActiveSheet.UsedRange
LastRowIndex = UsedRng.Row - 1 +
UsedRng.Rows.Count
Application.ScreenUpdating = False


For RowIndex = LastRowIndex To 1 Step -1
If
Application.CountA(Rows(RowIndex)) = 0 Then
Rows(RowIndex).Delete
End If
Next RowIndex


Application.ScreenUpdating = True


End Sub

Display More

**royUK** · May 19th 2021

Welcome to the Forum. Please read the Forum Rules to understand how the Forum works and why I have added Code Tags to your post

All VBA code posted in the forum must be wrapped in code tags, which you omitted, including single-line code snippets.Be sure to use them in future posts.

How to use code tags

Just highlight all of the code and press the <> in the post menu above button to add the code tags.

Thanks.

**royUK** · May 19th 2021

Attach an example workbook

Chitaah · May 19th 2021

Hi Roy,

Thanks a lot and rectifying my mistakes. It was unintentional. From the next time I will definitely follow whenever I post any code and go by the rules only.

Thanks,

Somnath

**royUK** · May 19th 2021

Can you attach an example workbook

Chitaah · May 19th 2021

Hi Roy,

Actually the The workbook is populated with company data that's why I could not upload it. But I have expressed what I need to know and the code which is working. I just need to check if any rows increased in A coloum in the source sheet and if so then start search for the "Ferro Manganese" in that which is already mentioned in my if condition..if found then already the copy and paste code is working...Only the extra condition of increased row I need the help.

Need to copy from sourcesheet to destination sheet only the new data entered based on serial no on the source sheet

Participate now!

Share

Tags