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
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
LEVERAGE1.jpg )
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
Reply With Quote
Bookmarks