PDA

View Full Version : Copy paste the data by vba



shivanis
07-24-2019, 01:12 AM
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

xld
07-24-2019, 03:11 AM
No attachments.

p45cal
07-24-2019, 09:30 AM
Similar, earlier (Cross) post at https://www.mrexcel.com/forum/excel-questions/1104405-copy-paste-vba.html

shivanis
07-27-2019, 09:40 PM
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
07-28-2019, 02:05 AM
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
07-28-2019, 10:48 AM
Thnx Alot Sir/Mam p45cal ,xld for giving ur precious time and great support to this post
Have a Great Day Sir/Mam