Copying data from one worksheet to another according to criteria

[Harry_R]

Hi there.

I have two worksheets. One of them (“Output file”) I need to update every day based on the data from the other worksheet (“Source file”).

I only need to copy in data which I have in the “Source File”: for Julia, David and Kate and only for the dates I have in there. In the “Output File” there are more names and dates.

At the end I need to replace formulas with values, so that there would be no formulas in the “Output File”.

My approach is to first find the specific Name in the “Output File” and then populate this row with values using Index Match (corresponding to the right name and date). I am new to VBA and I am not sure whether this is the correct approach or there could be other solution. Anyway, the code that I wrote does not work: the IndexMatch formula is not working.

The code is in the "Source File"

Please help!

Thanks in advance.
Here is my code:

Sub Main()

Dim rng As Range
Dim C As Range
Dim g As Range
Dim y As Range
Dim x As Range


Workbooks("Source File.xlsm").Sheets("Source").Activate

Set g = Worksheets("Source").Range("B2:h5")
Set y = Worksheets("Source").Range("B2:B5")
Set x = Worksheets("Source").Range("B2:h2")

Workbooks("Output File.xlsx").Sheets("Output").Activate


With Worksheets("Output").Range("a5:a10")
  Set C = .Find("Julia", LookIn:=xlValues)
  For i = 1 To 10
  C.Offset(, i).Formula = "=iferror(index(g,match(a5,y,0),match(b4,x,0)),0)"  'this does not work
  Next i
  
  'need the same procedure also for David and Kate
End With

'at the end, the inserted formulas need to be replaced with values

End Sub

 

[vletm]

Harry_R
the 1st sample version ...
1) Open Your 'Source File.xlsm
2) Open this 'Output File.xlsb'
3) Press [ Do It ]-button
... something like this?
... Ideas? ... Questions?

 

[Harry_R]

Hi,
Yes, it was a mistake. I corrected with the right dates.
And it is perfect! It works like magic!
Thank you!!

 

[Harry_R]

Hi,
One more question: Before copying the values from the Source File to the Output file, how can I replace old values in the Output file with zeros for Julia, David and Kate in columns B-K. Basically, each time when I do an update, I want to start with an empty table before populating it with data.
Many thanks in advance!

 

[vletm]

Harry_R .. hmm?
I modified code ...
1) press [ Open File & Copy ]
2) select needed 'Source File' = any file which has Your data

This will open file and
if/then finds name from 'Source File'
the code would clean 1st old data away from 'OutPut'.
( no matter which names are in Your 'Source File', better to have valid names! )

 

[Harry_R]

Thank you! Highly appreciated. I will try to implement it to my files.