VBA Simple Copy and Paste between two workbooks based on criteria

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/showth...ll=1#post11433
school1.xlsx is ap.xls
school2.xlsx is leverage.xlsx (LEVERAGE1.xlsx )

Quote:

---

Originally Posted by fixer

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
Attachment 2373
Alan

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/watch?v=SIDLFRkUEIo&lc=UgzTF5vvB67Zbfs9qvx4AaABAg
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=UgzMCQUIQgrbec400jl4AaABAg
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=vXyMScSbhk4&lc=Ugxa2VYHMWJWXA6QI294AaABAg. 9irLgSdeU3r9itU7zdnWHw
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=Ugw6UrV69zpeKvLOeOV4AaABAg. 9ht16tzryC49htJ6TpIOXR
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgwMKwGZpDjv7vi7pCx4AaABAg
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw6UrV69zpeKvLOeOV4AaABAg. 9ht16tzryC49htOKs4jh3M
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgxVW-am20rQ5GFuJ9F4AaABAg
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

Hello

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

Quote:

---

Originally Posted by fixer

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
Attachment 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
Attachment 2375Attachment 2378

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






Alan

Doc Sir that was just an example Sir

Quote:

---

Originally Posted by fixer

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/showth...ll=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

---

Quote:

---

Originally Posted by fixer

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
Attachment 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

Code:

---

'
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

---

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/watch?v=SIDLFRkUEIo&lc=UgzTF5vvB67Zbfs9qvx4AaABAg
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=UgzMCQUIQgrbec400jl4AaABAg
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=vXyMScSbhk4&lc=Ugxa2VYHMWJWXA6QI294AaABAg. 9irLgSdeU3r9itU7zdnWHw
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=Ugw6UrV69zpeKvLOeOV4AaABAg. 9ht16tzryC49htJ6TpIOXR
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgwMKwGZpDjv7vi7pCx4AaABAg
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw6UrV69zpeKvLOeOV4AaABAg. 9ht16tzryC49htOKs4jh3M
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgxVW-am20rQ5GFuJ9F4AaABAg
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA