pull data from multiple workbook after matching based on headers



I  have  this  code  works  well  based  on  headers  but  the  problem  I  need  matching  the  headers  BRAND , BATCH and PR for  all the  files  with  file  result1  and  brings  the  values  based on  headers  as  in  my  file  result1  in  sheet  before     so  i  search  so  much  to  adapting  the  code  what  i  need  or  search  macro is   close  what  i  want  in the  internet  but  i  don't  find  any  thing 

so  i  put  the  orginal  data  in  sheet  before  and  the  expected  result  in sheet  after  in  file result1  by  the  way  it  remains  the  file4  I  can't   upload  because  the  limits 3  files     but  the  file4  contains  the  values  for  purchase  as  in  file  result1  may be  you  ask  me  where did  get  the  values from

finally  i  need  some  help  to  guide  me  how  mod  the  code 

Option Explicit

 Sub Import_Source_File_Columns()

    Dim sourceWb As Workbook, sourceSheet As Worksheet
    Dim col As Long
    Dim foundCol As Variant

    Set sourceWb = Workbooks.Open(ThisWorkbook.Path & "\Source workbook.xlsx")  'CHANGE THIS
    Set sourceSheet = sourceWb.Worksheets(1)

    With ThisWorkbook.Worksheets(1)
        For col = 1 To .Cells(1, .Columns.Count).End(xlToLeft).Column
            foundCol = Application.Match(.Cells(1, col).Value, sourceSheet.Rows(1), 0)
            If Not IsError(foundCol) Then
                sourceSheet.Columns(foundCol).Copy .Columns(col)
            End If
    End With

    sourceWb.Close SaveChanges:=False

End Sub

thanks in advance  




Are columns A, B, C and D the same in all your source files (so rows are the same)? If so, it will be easy to copy column N purchase (and others from file 4) to the results from your macro.

Note: I could not use your file results1.xlsm file directly (but copied Before sheet and module into a new workbook, which worked).
John_Ru (rep: 1072) Apr 22, '21 at 11:30 am
thanks  for  follow  my  post   
about  your  question columns A, B, C and D the same in all  source files,  absolutely  they  are  different  in  ranges  in four  files , that's  why  i  ask  bring  the  values  after  matching  based on headers  about  the  matching  should  search  for  headers   BRAND , BATCH and PR  then  brings  the  values   to  the  rests  of  headers(sales,returns,purchas,stock) after matching   an  put  the  result  in  a new  workbook  if  you  see  that's  difficult  or  impossible    then   I  have   to  rearrange  data  in  the  same  ranges   but  it  takes  from  me  more  time  so  I 'm  open  for  all  suggestion   ,you  can  do  that by  the  right  way  what  you  see
Hasson Apr 22, '21 at 2:49 pm
Sorry. I should have said "are rows similar?" since cells A1:D8 of the first source are identical to cells J1:N8 of source book 2. 

Sorry but your sparse punctuation makes it difficult for me to follow the situation you describe. 
John_Ru (rep: 1072) Apr 22, '21 at 4:45 pm
so  what  exactly  want    , may  be  i  mod   the  files 
give  me  what  in  your  mind  
Hasson Apr 22, '21 at 5:03 pm
What is the key field for combining the data rows from the three source files then? Is it the combination of all three cells- BRAND, BATCH and PR? Also is it possible that the same combination might not be present in all files (e.g. if there is no stock or there have been no sales)?
I assume this is just test data so how many items (combinations) do you need to handle in the real world?

John_Ru (rep: 1072) Apr 23, '21 at 12:06 pm
about  question key field   , it's the combination of all three cells- BRAND, BATCH and PR how  you  use   the  standard   , if   you  look to some  items  it  repeates  in cols BRAND,BATCH  but   the  different  in  COL  PR   ,  so   I  assume  the  code   should  see  to  the  three  COUMNS together  the  COL  PR  specifies  the  different between   repeated items  but  it  should  see to  the  COL BRAND ,BATCH  together   if  you  see  this  is  impossible   I accept  any  suggestion  for  me   I  don't  want making   hard the  matter for  you  just  i  would  make clear  my  idea  if   it's  possible  do  that , if  it's  not  , please  tell  me  the  right way  and  i  accept  that  about  the  same combination might not be present in all files I can  say   absolutely  yes  finally  about   my  data   you're  right ,  my  real   data  are  about  1000  items just   attach a simple  data  to  test data
note:  I  don't  want  make  matter  difficult   if  you  see  my   post  is  complicated  I  accept  any  suggestion  to  solve  my  problem  for this  project  thanks  for  your  cooprate
Hasson Apr 23, '21 at 12:57 pm
Hasson. I don't have any more time today to look at this but it can be done.

Given you have 1,000 items or so in several workbooks, I will probably use arrays in VBA (so you don't have to wait a long time the results).

I may have some time this weekend to look at the pronblem but you may get an answer before (from other contributors) 
John_Ru (rep: 1072) Apr 23, '21 at 1:20 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login