Convert an equation for transferring data from one file to another separate file to Vba Code

[Hany ali]

Hello my dear,i want Your Help to Convert this Equation to Vba Code ,to Moving Data from Workbook (BikoData.xlsx ) to another workBook (Weekly Report For Profit.xlsm)

=IFERROR(IF(INDEX('C:\Users\hany.ali\Desktop\Weekly Report For Profit - Copy\[BikoData.xlsx]Data'!$A$2:$AM$1000,ROWS(A$1:A1),MATCH(B$1,'C:\Users\hany.ali\Desktop\Weekly Report For Profit - Copy\[BikoData.xlsx]Data'!$A$1:$AM$1,0))=0,"",INDEX('C:\Users\hany.ali\Desktop\Weekly Report For Profit - Copy\[BikoData.xlsx]Data'!$A$2:$AM$1000,ROWS(A$1:A1),MATCH(B$1,'C:\Users\hany.ali\Desktop\Weekly Report For Profit - Copy\[BikoData.xlsx]Data'!$A$1:$AM$1,0))),"")

This equation made the file very heavy and I hope to find a suitable code for transferring data as this equation works under the same conditions .. by as the Same this Headers

 

[p45cal]

A completely blank workbook BikoData.xlsx. Great.

 

[Hany ali]

sorry my dear
Please Delete Empty File From Main Post and Paste This File instead of it in Main Post

 

[p45cal]

If, in your real files you have a column headed Voucher in the Data sheet then

Sub blah()
    Set wbSce = Workbooks.Open("C:\Users\hany.ali\Desktop\Weekly Report For Profit - Copy\BikoData.xlsx", ReadOnly:=True)
    Workbooks("BikoData.xlsx").Sheets("Data").Range("A1:AM1000").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=ThisWorkbook.Sheets("Basic").Range("B1:S1"), Unique:=False
    wbSce.Close False
End Sub

might do it.

 

[p45cal]

…otherwise:

Sub blah2()
 With Sheets("Basic").Range("B2:S500")
 .FormulaR1C1 = "=IFERROR(IF(INDEX('C:\Users\hany.ali\Desktop\Weekly Report For Profit - Copy\[BikoData.xlsx]Data'!R2C1:R1000C39,ROWS(R1C[-1]:R[-1]C[-1]),MATCH(R1C,'C:\Users\hany.ali\Desktop\Weekly Report For Profit - Copy\[BikoData.xlsx]Data'!R1C1:R1C39,0))=0,"""",INDEX('C:\Users\hany.ali\Desktop\Weekly Report For Profit - Copy\[BikoData.xlsx]Data'!R2C1:R1000C39,ROWS(R1C[-1]:R[-1]C[-1]),MATCH(R1C,'C:\Users\hany.ali\Desktop\Weekly Report For Profit - Copy\[BikoData.xlsx]Data'!R1C1:R1C39,0))),"""")"
 .Value = .Value
 End With
End Sub

 

[p45cal]

I only used the same path as in your first message, if you've moved it then you need to update the code yourself.
Workbooks.Open("C:\Users\hany.ali\Desktop\Weekly Report For Profit - Copy\BikoData.xlsx"

 

[p45cal]

I tested both code snippets here, they worked; (the first code snippet I provided needed to see the persence of a Voucher column in the source data).
You just need to get your file locations 100% correct.
I notice there's a dot between hany and ali - but only sometimes.

 

[Hany ali]

The first code worked with me by this way

Sub blah()
   Set wbSce = Workbooks.Open("C:\Users\hany ali\Desktop\Weekly Report For Profit\BikoData.xlsx", ReadOnly:=True)
   Workbooks("BikoData.xlsx").Sheets("Data").Range("A1:AM1000").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=ThisWorkbook.Sheets("Basic").Range("B1:S1"), Unique:=False
  wbSce.Close False
End Sub

thanks alot my dear allready i used first code and now work well
Really unable to thank you - that's exactly what is needed
I got tired of you so much with me