copy and past one file to multi file.

[faizan haq]

Hi,

I have unique value in A1:A10 in master 1 file and i need to paste one by one value into multiple files which is available in folder it should be done by VBA automatic,

all work on sheet1
step
1. copy (A1) value from master file
2. open 1 file from folder
3. paste on (B10)
4. save and close
5. copy (A2) value from master file
6. open 2 file from folder
7. paste on (B10)
8. save and close

Value	need to paste value in multi files and cell range is common (B10)
CO1	1 filenam.xls
CO2	2 filenam.xls
CO3	3 filenam.xls
CO4	4 filenam.xls
CO5	5 filenam.xls
CO6	6 filenam.xls

 

[DanteAmor]

Hi and welcome to MrExcel!

Try this, You put the macro file in the same folder where the files are..

Sub copy_value_in_file()
  Dim i As Long, sPath As String, sFile As String, sh As Worksheet
  Application.ScreenUpdating = False
  
  Set sh = ActiveSheet
  sPath = ThisWorkbook.Path & "\"
  For i = 1 To 10
    sFile = Range("B" & i).Value
    If sFile <> "" And Dir(sPath & sFile) <> "" Then
      Workbooks.Open (sPath & sFile)
      Range("B10").Value = sh.Range("A" & i).Value
      ActiveWorkbook.Close True
    End If
  Next
End Sub

 

[faizan haq]

Hi Dante,

thanks for reply. but showing error, i want to more clarify that what i want. please check attached file and advice accordingly.

 

[faizan haq]

Hello,

waiting for your advice.

 

[DanteAmor]

You put the macro file in the same folder where the files are..

the file name with extension you must put it in column B. So you asked for it in your original post.

Please: Do not modify the macro.

 

[faizan haq]

Hi,
Great its working, its is possible that we dont need to paste file name in excel column B, its run by auto?