Open location and update the column range in VBA

[Rajendra_kodavaty]

Hey folks,

I am a novice to excel vba. I need help from experts like you
I have to develop a macro, where i could update the values of closing stock on weekly basis.
Lets consider the macro excel file as Macro1.xlsm and the file containing the closing stock Clsg2.xlsx

My methodology is simple.

1. First i download all the master stock dump to "Col A (SAP Code)" and "Col B (Description)" in Macro1.xlsm
2. I need vba code to open the file location of closing stock which is supposed to appear in "Col C" of Macro1.xlsm
3. From the selected file (Ex: Clsg2.xlsx) i have to lookup the values in column number 6 of Clsg2.xlsx and paste the values in "COL C" of Macro1.xlsm
4. The range of Clsg2.xlsx in Column number 6 is dynamic(Changes weekly).
5. After pasting values in "COL C" you will observe only the values of closing stock in Clsg2.xlsx file will be pasted in "COL C" and remaining will return "#NA".
6. All the rows with "#NA" has to be deleted

If there is any other way to do it, please explain
Thank you Very much!