need help on vba code which work corresponding files depends upon file name

[breadwinner]

Dear expert

i have set of data range in master A:H i want split value in a col combination of hypen values to closed workbooks of A,B,C,D,....Etc depends upon list of filenames in col H:H.

like if file name is A.xlsb in H col this code run to particular row to A workbook

For Each cell In Sheets("Master").Range("A2:A200") *need to change master sheet as active workbook

      If InStr(cell, "-451414-") Then Sheets("RECK").Cells(65000, "E").End(xlUp).Offset(1).Resize(, 12) = Application.Index(Split(Join(Array(Replace$(Replace$(cell, "-0-", "-000000-"), "-", "|"), "00000000", "000", "MAY-15", "USD", "AMOUNT", cell.Offset(, 3), "NO"), "|"), "|"), Array(1, 3, 2, 4, 5, 6, 7, 8, 9, 10, 11, 12))
  Next cell
End With

Find the attachment.

[breadwinner]

any experts help on this need urgently!! This thread already in above my post i wrong posted twice i could delete first one so please consider this actual thread!!

[jewelsharma]

Does this perform the desired operation:

Sub Sample()

Dim wbA As Workbook, wbB As Workbook, wbM As Workbook
Dim sFilePath As String, rCell As Range, sWB As String

Set wbM = ThisWorkbook 'The Master Workbook
sFilePath = "C:\Users\Admin\Desktop\Oracle\USD\FYD.xlsb" 'hard coded version (left unchanged from your code sample)
Set wbA = Workbooks.Open(Filename:=sFilePath & "\" & A.xlsb) 'Amend as necessary
Set wbB = 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(7, 0)
          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
     Next
End Sub

[breadwinner]

hi sir i getting error while run

[jewelsharma]

sFilePath = "C:\Users\Admin\Desktop\Oracle\USD\FYD.xlsb"

the above line should be amended to point to the directory/folder that has the "A.xlsb" and "B.xlsb' files.

So, if you have these files on your desktop, replace the above line of code with this one:

sFilePath = "C:\Users\Admin\Desktop"

[breadwinner]

Dear sir

sorry for disturbing you again i getting error 424 even though i saved A&B file in folder "F" and path is "C:\Users\Admin\Desktop\Oracle\Forum\Download\F"

Thanks

[jewelsharma]

I'm not sure, but the Directory Path maybe case-sensitive. You have a small "f" in the code, but a caps "F" in the path mentioned. Try changing "f" to "F" in the code.

[breadwinner]

No sir its small "f" only eventhough i getting error same i wrong quoted as "F". but it doesnt contrain the small or big alphs
this is path now C:\Users\Admin\Desktop\Oracle\Forum\Scrap In scrap folder i save all files

[breadwinner]

Sir last question if i add more files line up after A&B like C,D,E,F..Etc. How do i addi just try myself.
here

If wb.Name = "C.xlsb" Then Set wbB = wb
     If wb.Name = "D.xlsb" Then Set wbB = wb
here

If wbB Is Nothing Then Set wbB = Excel.Workbooks.Open(Filename:=sFilePath & "\" & "C.xlsb")
If wbB Is Nothing Then Set wbB = Excel.Workbooks.Open(Filename:=sFilePath & "\" & "D.xlsb")

here
 If sWB = "A.xlsb" Or sWB = "B.xlsb" Or sWB = "C.xlsb" Or sWB = "D.xlsb" Then

pls advice me sir

[jewelsharma]

firstly, declare new variables ... wbC as Workbook ... and so on, then add suitable conditions in the code (as you identified)

If wb.Name = "C.xlsb" Then Set wbC = wb

If wbC Is Nothing Then Set wbC = Excel.Workbooks.Open(Filename:=sFilePath & "\" & "C.xlsb")

If sWB = "A.xlsb" Or sWB = "B.xlsb" Or sWB = "C.xlsb" Or sWB = "D.xlsb" Then  'This approach is fine

[breadwinner]

Nice!! Amazing sir works perfectly!!!