Dear experts
I have set of macro to export rows to closed wb's depends upon file name meet wb's in column I:I in CodeA
and I have set of macro to export particular lines export to closed wb's depends upon file name meet wb's in column H:H in CodeB
but task is i want run CodeB task as CodeA procedure.
e.g take the wb's names from col I:I compare file in path move rows to correponding file as B
Here CodeA
Sub codea()
Dim mypath As String, wb As String, lastrow As Long
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Range("I:I").Copy Range("Z:Z")
Range("Z:Z").RemoveDuplicates Columns:=1, Header:=xlYes
lastrow = Range("Z" & Rows.Count).End(xlUp).Row
For x = 2 To lastrow
Range("A:I").AutoFilter field:=9, Criteria1:=Range("Z" & x)
With Range("A1").CurrentRegion
.Offset(1).Copy
wb = Range("Z" & x)
Workbooks.Open (mypath & "\" & wb)
Sheets("Master").Activate
With Range("F" & Rows.Count).End(xlUp).Offset(1)
.PasteSpecial Paste:=xlValues
End With
End With
ActiveWorkbook.Save
ActiveWorkbook.Close
Next
Range("A:I").AutoFilter
With Range("A1").CurrentRegion
.Offset(1).Delete
End With
HERE CodeB
Sub CodeB()
Dim wbA As Workbook, wbB As Workbook, wbM As Workbook, wb As Workbook
Dim sFilePath As String, rCell As Range, sWB As String
Set wbM = ThisWorkbook 'The Master Workbook
sFilePath = "C:\Test" 'hard coded version (left unchanged from your code sample)
For Each wb In Excel.Workbooks
If wb.Name = "A.xlsb" Then Set wbA = wb
If wb.Name = "B.xlsb" Then Set wbB = wb
Next
If wbA Is Nothing Then Set wbA = Excel.Workbooks.Open(Filename:=sFilePath & "\" & "A.xlsb") 'Amend as necessary
If wbB Is Nothing Then Set wbB = Excel.Workbooks.Open(Filename:=sFilePath & "\" & "B.xlsb") 'Amend as necessary
For Each rCell In wbM.Worksheets("Master").Range("A2:A200")
If InStr(rCell, "-451414-") > 0 Then
sWB = rCell.Offset(0, 7)
If sWB = "A.xlsb" Or sWB = "B.xlsb" Then
With Excel.Workbooks(sWB)
.Worksheets("REK").Range("E65000").End(xlUp).Offset(1).Resize(, 12) = _
Application.Index(Split(Join(Array(Replace$(Replace$(rCell, "-0-", "-000000-"), "-", "|"), "00000000", "000", "MAY-15", "USD", "AMOUNT", rCell.Offset(, 3), "NO"), "|"), "|"), Array(1, 3, 2, 4, 5, 6, 7, 8, 9, 10, 11, 12))
End With
End If
End If
Next
wbA.Close SaveChanges:=True
wbB.Close SaveChanges:=True
End Sub
find the attachment and wb TASKA&B have sheet tabs A,B,C,D,E,F consider as workbook saved in path
path :C:\Users\Admin\Desktop\test.
Bookmarks