Open file from location using VBA code - file name changes everytime.

Hi,
I need help to write a code to open a User name workbook from a location. This code should be in master file (Update_Report.xls) user will enter data in master file & press button to perform below actions.
Open perticular User name (Cell value B4 in Update_Report) excel workbook from location.
Copy data from master file.
Paste data in user file.
Save & close user file.

Code is as below. Code is not able to locate user file.

Sub Button8_Click()

  'Open user file & copy data

  FName = Range("B4")
  
  ChDir "C:\TEST\User Report"
    Workbooks.Open Filename:= _
        "C:\TEST\User Report\FName"
      Workbooks("Update_Report.xls").Activate
    Range("A1:B7").Copy
    Workbooks("FName").Activate
    Range("B5").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

End Sub

I am not able to upload master file due to some error. After I press button "Manage Attachments" nothing happens.

Please help me with this.
Thanks in advance.

SWapnil

Hello Swapnil.Kupwade,

Welcome to the Forum!

Since you changed the directory, both workbooks must be in the same directory. If not then the code should be...

Sub Button8_Click()

  'Open user file & copy data

  FName = Range("B4")
  
    Workbooks.Open Filename:= _
        "C:\TEST\User Report\FName"
      Workbooks("Update_Report.xls").Activate
    Range("A1:B7").Copy
    Workbooks("FName").Activate
    Range("B5").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

End Sub

Check that the file name in cell "B4" contains the file extension, i.e. ".xlsx", ".xlsm", etc.

Thanks
I have tried with ".xls" in cell B4. It is not working. Code gives error for FName not found. I have attached image.
I think I am missing define FName As Varient or similar thing. Please help me to solve the error.
I am sending folders with sample.

Thanks again for the help.

Regards,
SWapnil

FName Error.jpg
TEST.zip

Hello Swapnil.Kupwade,

This should work...

Sub Button8_Click()

    Dim FName As Variant

      ' Open user file & copy data
        FName = Range("B4")
  
        ChDir "C:\TEST\User Report"
    
        Workbooks.Open Filename:="C:\TEST\User Report\" & FName
        
      ' FName is now the ActiveWorkbook. The range does not need to be prefixed.
        Range("B5:C11").Value = Workbooks("Update_Report.xls").Range("A1:B7").Value
           
End Sub