PDA

View Full Version : VBA Simple Copy and Paste between two workbooks based on criteria


fixer
08-20-2019, 08:26 PM
we have to Open all the files all files are closed except the vba placed file
If column E of school.xlsx matches with column A of school2.xlsx then copy column E of school2.xslx and paste it to column Z of school1.xlsx and save and close all the files(close school1.xlsx and close school2.xlsx)
macro will be attached to a different file
all files are located in same place
example
cell E20 of school1.xlsx matches with column A10 of school2.xlsx then copy E10 of school2.xlsx and paste it to Z20 of school1.xlsx
Plz have a look i have attached my sample file also





Edit:
See post #3 .. http://www.excelfox.com/forum/showthread.php/2354-Copy-amp-Paste-by-a-Vba?p=11433&viewfull=1#post11433
school1.xlsx is ap.xls
school2.xlsx is leverage.xlsx (LEVERAGE1.xlsx )
DocAElstein
08-24-2019, 04:13 PM
we have to Open all the files all files are closed except the vba placed file
If column E of school.xlsx matches with column A of school2.xlsx then copy column E of school2.xslx and paste it to column Z of school1.xlsx and save and close all the files(close school1.xlsx and close school2.xlsx)
macro will be attached to a different file
all files are located in same place
example
cell E20 of school1.xlsx matches with column A10 of school2.xlsx then copy E10 of school2.xlsx and paste it to Z20 of school1.xlsx
Plz have a look i have attached my sample file also :confused:

Hello,

Possibly you have uploaded the wrong files ?????
What is the relevance of ap.xls and Leverage.xlsx

???? Wrong Files Uploaded.JPG: https://imgur.com/XoZ6O5S
2373
Alan
fixer
08-24-2019, 08:06 PM
I by mistakely changed the file i got confused
here
school1.xlsx is ap.xls
school2.xlsx is leverage.xlsx



https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://www.youtube.com/watch?v=SIDLFRkUEIo&lc=UgzTF5vvB67Zbfs9qvx4AaABAg (https://www.youtube.com/watch?v=SIDLFRkUEIo&lc=UgzTF5vvB67Zbfs9qvx4AaABAg)
https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgxLtKj969oiIu7zNb94AaABAg (https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgxLtKj969oiIu7zNb94AaABAg)
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg (https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg)
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg (https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg)
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgzMCQUIQgrbec400jl4AaABAg (https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgzMCQUIQgrbec400jl4AaABAg)
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz0Uy2bCSCTb1W-0_14AaABAg (https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz0Uy2bCSCTb1W-0_14AaABAg)
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugx12mI-a39T41NaZ8F4AaABAg.9iDQqIP56NV9iFD0AkeeJG (https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugx12mI-a39T41NaZ8F4AaABAg.9iDQqIP56NV9iFD0AkeeJG)
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=Ugxa2VYHMWJWXA6QI294AaABAg.9irLgSdeU3r9itU7zdnW Hw (https://www.youtube.com/watch?v=vXyMScSbhk4&lc=Ugxa2VYHMWJWXA6QI294AaABAg.9irLgSdeU3r9itU7zdnW Hw)
https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgzFkoI0n_BxwnwVMcZ4AaABAg (https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgzFkoI0n_BxwnwVMcZ4AaABAg)
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz0Uy2bCSCTb1W-0_14AaABAg.9htChVuaX9W9htG01cKBzX (https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz0Uy2bCSCTb1W-0_14AaABAg.9htChVuaX9W9htG01cKBzX)
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw6UrV69zpeKvLOeOV4AaABAg.9ht16tzryC49htJ6TpIO XR (https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw6UrV69zpeKvLOeOV4AaABAg.9ht16tzryC49htJ6TpIO XR)
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgwMKwGZpDjv7vi7pCx4AaABAg (https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgwMKwGZpDjv7vi7pCx4AaABAg)
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw6UrV69zpeKvLOeOV4AaABAg.9ht16tzryC49htOKs4jh 3M (https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw6UrV69zpeKvLOeOV4AaABAg.9ht16tzryC49htOKs4jh 3M)
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgxVW-am20rQ5GFuJ9F4AaABAg (https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgxVW-am20rQ5GFuJ9F4AaABAg)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
DocAElstein
08-25-2019, 12:16 PM
Hello

Your files are still mixed up!!!!……………..


school1.xlsx is ap.xls
school2.xlsx is leverage.xlsx
So… I assume … we have to Open all the files all files are closed except the vba placed file
If column E of ap.xls matches with column A of leverage.xlsx then copy column E of leverage.xlsx and paste it to column Z of ap.xls and save and close all the files(close ap.xls and close leverage.xlsx)
macro will be attached to a different file
all files are located in same place
example
cell E20 of ap.xls matches with column A10 of leverage.xlsx then copy E10 of leverage.xlsx and paste it to Z20 of ap.xls

( I further assume that leverage.xlsx is LEVERAGE1.xlsx
LEVERAGE1.jpg : https://imgur.com/VO11YcO
2377 )

………but your files are still mixed up……. You still got confused!!!!

There is nothing in cell E20 of ap.xls
nothing in cell E20 of ap.JPG : https://imgur.com/A0J1KUD
23752378

There is nothing in cell A10 of LEVERAGE1.xlsx
nothing in cell A10 of LEVERAGE1.JPG : https://imgur.com/A0J1KUD
23762379






Alan
fixer
08-25-2019, 01:35 PM
Doc Sir that was just an example Sir
DocAElstein
08-26-2019, 12:54 PM
Doc Sir that was just an example Sir
Please in future give a working example with data. This would be helpful for testing

For example… http://www.excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=11438&viewfull=1#post11438
example
cell E2 of ap.xls matches with column A3 of leverage.xlsx then copy E3 of leverage.xlsx and paste it to Z2 of ap.xls





school1.xlsx is ap.xls
school2.xlsx is leverage.xlsx
So… I assume … we have to Open all the files all files are closed except the vba placed file
If column E of ap.xls matches with column A of leverage.xlsx then copy column E of leverage.xlsx and paste it to column Z of ap.xls and save and close all the files(close ap.xls and close leverage.xlsx)
macro will be attached to a different file
all files are located in same place
example
cell E20 of ap.xls matches with column A10 of leverage.xlsx then copy E10 of leverage.xlsx and paste it to Z20 of ap.xls

( I further assume that leverage.xlsx is LEVERAGE1.xlsx
LEVERAGE1.jpg : https://imgur.com/VO11YcO
2377 )

All files are in the same folder, … so I further assume that the file with the macro in is also in this same place, ( so my macro can use ThisWorkbook.Path to return the path that I need to open the files )


Two alternative solutions for you
Sub Vixer5a()
This is a simple spreadsheet interaction macro.

Sub Vixer5b()
This uses VBA arrays. It will be faster for lots of data rows: We take in all dat a into VBA arrays. We do all workings with those arrays. Then results are pasted out in one go



'
Sub Vixer5a() ' http://www.excelfox.com/forum/showthread.php/2354-Copy-amp-Paste-by-a-Vba?p=11429&viewfull=1#post11429 http://www.excelfox.com/forum/showthread.php/2354-Copy-amp-Paste-by-a-Vba?p=11433&viewfull=1#post11433
Rem 1 Workbook and worksheets info
'1a) Workbook info
Dim Wbm As Workbook: Set Wbm = ThisWorkbook ' The workbook containing macro
Dim Wb1 As Workbook, Wb2 As Workbook ' (These will be set later when the workbooks are opened)
Dim strWb1 As String: Let strWb1 = "ap.xls"
Dim strWb2 As String: Let strWb2 = "LEVERAGE1.xlsx"
'1b) Worksheets info
Dim Ws1 As Worksheet, Ws2 As Worksheet ' (These will be set later when the workbooks are opened)
Dim Lr1 As Long, Lr2 As Long: Let Lr1 = 14: Lr2 = 8 ' For sample file
Rem 2 Open files ..... we have to Open all the files all files are closed except the vba placed file
' Workbooks.Open Filename:="F:\Excel0202015Jan2016\ExcelFox\vixer\Von Vixer\ap.xls"
' Workbooks.Open Filename:="F:\Excel0202015Jan2016\ExcelFox\vixer\Von Vixer\LEVERAGE1.xlsx"
Workbooks.Open Filename:=ThisWorkbook.Path & "\" & strWb1
Set Wb1 = ActiveWorkbook ' The workbook just opened will now be the current active workbook
Set Ws1 = Wb1.Worksheets.Item(1)
Workbooks.Open Filename:=ThisWorkbook.Path & "\" & strWb2
Set Wb2 = ActiveWorkbook '
Set Ws2 = Wb2.Worksheets.Item(1)
Rem 3
Dim Rws1 As Long
For Rws1 = 2 To Lr1 ' outer loop for all rows in ap.xls ==============================
Dim Rws2 As Long
For Rws2 = 2 To Lr2 ' inner loop for all rows in leverage1 -------------------
If Ws1.Range("E" & Rws1 & "").Value = Ws2.Range("A" & Rws2 & "").Value Then ' If column E of ap.xls matches with column A of leverage.xlsx then ...
Let Ws1.Range("Z" & Rws1 & "").Value = Ws2.Range("E" & Rws2 & "").Value ' ... copy column E of leverage.xlsx and paste it to column Z of ap.xls
Else
End If
Next Rws2 ' ------------------------------------------------------------------
Next Rws1 ' ================================================== ========================
Rem 4 close the files
Wb2.Close
Wb1.Close savechanges:=True
End Sub

'
Sub Vixer5b() ' http://www.excelfox.com/forum/showthread.php/2354-Copy-amp-Paste-by-a-Vba?p=11429&viewfull=1#post11429 http://www.excelfox.com/forum/showthread.php/2354-Copy-amp-Paste-by-a-Vba?p=11433&viewfull=1#post11433
Rem 1 Workbook and worksheets info
'1a) Workbook info
Dim Wbm As Workbook: Set Wbm = ThisWorkbook ' The workbook containing macro
Dim Wb1 As Workbook, Wb2 As Workbook ' (These will be set later when the workbooks are opened)
Dim strWb1 As String: Let strWb1 = "ap.xls"
Dim strWb2 As String: Let strWb2 = "LEVERAGE1.xlsx"
'1b) Worksheets info
Dim Ws1 As Worksheet, Ws2 As Worksheet ' (These will be set later when the workbooks are opened)
Dim Lr1 As Long, Lr2 As Long: Let Lr1 = 14: Lr2 = 8 ' For sample file
Rem 2 Open files ..... we have to Open all the files all files are closed except the vba placed file
' Workbooks.Open Filename:="F:\Excel0202015Jan2016\ExcelFox\vixer\Von Vixer\ap.xls"
' Workbooks.Open Filename:="F:\Excel0202015Jan2016\ExcelFox\vixer\Von Vixer\LEVERAGE1.xlsx"
Workbooks.Open Filename:=ThisWorkbook.Path & "\" & strWb1
Set Wb1 = ActiveWorkbook ' The workbook just opened will now be the current active workbook
Set Ws1 = Wb1.Worksheets.Item(1)
Workbooks.Open Filename:=ThisWorkbook.Path & "\" & strWb2
Set Wb2 = ActiveWorkbook '
Set Ws2 = Wb2.Worksheets.Item(1)
'2b) VBA arrays
Dim arr1() As Variant, arr2() As Variant ' In the next line we use the Property "method" .Value assignment to get all values in one go. This returns the field values held in variant types, so we musr Dim appropriately in order to avoid a type mismatch error
Let arr1() = Ws1.Range("A1:Z" & Lr1 & "").Value: Let arr2() = Ws2.Range("A1:E" & Lr2 & "").Value
Rem 3
Dim Rws1 As Long
For Rws1 = 2 To Lr1 ' outer loop for all rows in ap.xls ==============================
Dim Rws2 As Long
For Rws2 = 2 To Lr2 ' inner loop for all rows in leverage1 -------------------
If arr1(Rws1, 5) = arr2(Rws2, 1) Then ' If column E of ap.xls matches with column A of leverage.xlsx then ...
Let arr1(Rws1, 26) = arr2(Rws2, 5) ' ... copy column E of leverage.xlsx and paste it to column Z of ap.xls
Else
End If
Next Rws2 ' ------------------------------------------------------------------
Next Rws1 ' ================================================== ========================
'3b) paste out all in go
Ws1.Range("A1:Z" & Lr1 & "").Value = arr1()
Rem 4 close the files
Wb2.Close
Wb1.Close savechanges:=True
End Sub
fixer
08-28-2019, 09:42 AM
Thnx Alot Doc Sir for giving ur precious time and Great Support to this post
Have a Great Day Sir

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://www.youtube.com/watch?v=SIDLFRkUEIo&lc=UgzTF5vvB67Zbfs9qvx4AaABAg (https://www.youtube.com/watch?v=SIDLFRkUEIo&lc=UgzTF5vvB67Zbfs9qvx4AaABAg)
https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgxLtKj969oiIu7zNb94AaABAg (https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgxLtKj969oiIu7zNb94AaABAg)
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg (https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg)
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg (https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg)
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgzMCQUIQgrbec400jl4AaABAg (https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgzMCQUIQgrbec400jl4AaABAg)
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz0Uy2bCSCTb1W-0_14AaABAg (https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz0Uy2bCSCTb1W-0_14AaABAg)
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugx12mI-a39T41NaZ8F4AaABAg.9iDQqIP56NV9iFD0AkeeJG (https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugx12mI-a39T41NaZ8F4AaABAg.9iDQqIP56NV9iFD0AkeeJG)
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=Ugxa2VYHMWJWXA6QI294AaABAg.9irLgSdeU3r9itU7zdnW Hw (https://www.youtube.com/watch?v=vXyMScSbhk4&lc=Ugxa2VYHMWJWXA6QI294AaABAg.9irLgSdeU3r9itU7zdnW Hw)
https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgzFkoI0n_BxwnwVMcZ4AaABAg (https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgzFkoI0n_BxwnwVMcZ4AaABAg)
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz0Uy2bCSCTb1W-0_14AaABAg.9htChVuaX9W9htG01cKBzX (https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz0Uy2bCSCTb1W-0_14AaABAg.9htChVuaX9W9htG01cKBzX)
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw6UrV69zpeKvLOeOV4AaABAg.9ht16tzryC49htJ6TpIO XR (https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw6UrV69zpeKvLOeOV4AaABAg.9ht16tzryC49htJ6TpIO XR)
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgwMKwGZpDjv7vi7pCx4AaABAg (https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgwMKwGZpDjv7vi7pCx4AaABAg)
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw6UrV69zpeKvLOeOV4AaABAg.9ht16tzryC49htOKs4jh 3M (https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw6UrV69zpeKvLOeOV4AaABAg.9ht16tzryC49htOKs4jh 3M)
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgxVW-am20rQ5GFuJ9F4AaABAg (https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgxVW-am20rQ5GFuJ9F4AaABAg)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)