Copy paste the data by vba

[shivanis]

If column E of sample1.xls matches with column A of sample2.xlsx then look column O of sample1.xls and column P of sample1.xls & if column O of sample1.xls is higher then calculate the 0.50% of column O of sample1.xls or if column P of sample1.xls is higher then calculate the 0.50% of that and multiply the same with column L of sample1.xls(column L can contain - minus sign so ignore that or it will not contain any sign with numbers so we have to look only the numbers) and add that data to Column R of sample1.xls and paste the result to sample2.xlsx from column C(if column C has data then paste to column D and if Column D has then column E and so on)
Vba is placed in a different file
all files are located in same path

sample1.xls and sample2.xlsx has headers so ignore the first row

[Bob Phillips]

No attachments.

[p45cal]

Similar, earlier (Cross) post at https://www.mrexcel.com/forum/excel-...paste-vba.html

[shivanis]

Sir this is not a cross posted question plz have a relook into my post
i have attached my sample file plz have a look

[p45cal]

See attached, which contains:

Sub blah()
Dim wb1 As Workbook, wb2 As Workbook, sh1 As Worksheet, sh2 As Worksheet, c As Range, fPath As String, RngSceData As Range, RngMySmbol As Range, DestColm As Long
fPath = ThisWorkbook.Path & "\"
Set wb1 = Workbooks.Open(fPath & "ap.xls")    'Verify file name
Set wb2 = Workbooks.Open(fPath & "PL.xlsx")    'Verify file name
Set sh1 = wb1.Sheets(1)
Set sh2 = wb2.Sheets(1)
Set RngSceData = Intersect(sh1.Columns("E"), sh1.Range("A1").CurrentRegion)
Set RngSceData = Intersect(RngSceData, RngSceData.Offset(1))

For Each c In sh2.Range("A2", sh2.Cells(Rows.Count, 1).End(xlUp)).Cells
  Set RngMySmbol = Nothing
  Set RngMySmbol = RngSceData.Find(what:=c.Value, Lookat:=xlWhole, LookIn:=xlFormulas, searchformat:=False)
  If Not RngMySmbol Is Nothing Then
    DestColm = Application.Max(3, sh2.Cells(c.Row, sh2.Columns.Count).End(xlToLeft).Column + 1)
    sh2.Cells(c.Row, DestColm).Value = Application.Max(RngMySmbol.Offset(, 10).Resize(, 2)) * 0.005 * Abs(RngMySmbol.Offset(, 7).Value) + RngMySmbol.Offset(, 13).Value
  End If
Next c

wb1.Close False
wb2.Close True
End Sub

[shivanis]

Thnx Alot Sir/Mam p45cal ,xld for giving ur precious time and great support to this post
Have a Great Day Sir/Mam