title: How to get file path from different sheet in VBA
body: <p>I am new in VBA. I have two sheets i.e. sheet1 has path of input Directory and Output directory.
  On sheet2 i am started to code. i wants to read data from multiple xls files that are are stores in a folder . this folder path is specified in sheet1. i wants to copy all files headers from all xls files to sheet3 in a column.
  my code is working well but i have given file path directly but i wants to get it from other sheet. Please help me. Below is my code and i have attched sheet1.<a href="https://i.stack.imgur.com/NlJ4P.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/NlJ4P.jpg" alt="Sheet1 Data"></a></p>
  
  <pre><code>Public Sub CommandButton1_Click()
  'DECLARE AND SET VARIABLES
  Dim wbk As Workbook
  Dim Filename As String
  Dim Path As String
  Dim mainwb As Workbook
  Dim ws As Worksheet
   Dim search_result As Range   'range search result
      Dim blank_cell As Long
  Dim wb As Workbook
  Path = "D:\Testing\Data\Input\"
  Filename = Dir(Path &amp; "*.xls")
  '--------------------------------------------
  'OPEN EXCEL FILES
   Do While Len(Filename) &gt; 0  'IF NEXT FILE EXISTS THEN
      Set wbk = Workbooks.Open(Path &amp; Filename)
      'MySheet = Application.Caller.Worksheet.Name
      'Set sh = MySheet()
     'Variable = ActiveSheet.Name
    ' Sheets(Variable).Range("A1:D1").Copy
      'Sheets("Sheet2").Column(B2).Select.Activate.Paste
     ' Sheets("Sheet2").Active
      'Columns("B2").Select
    Set wbk = ActiveWorkbook
    Variable = ActiveSheet.Name
    wbk.Sheets(Variable).Rows(1).EntireRow.Copy
  
   Workbooks("Tool.xlsm").Activate
  ' Workbooks("DFT Tool.xlsm").Sheets("Sheet2").Activate
  'ActiveWorkbook.ActiveSheet
  
    'Sheets("Sheet2").Activate
      'ActiveSheet.Columns("E").Select
    ' Range("E1").End(xlDown).Offset(1, 0).Select
      'ActiveSheet.Paste
  
   Set wb = ActiveWorkbook
    Set ws = wb.Sheets("Sheet2")
      For Each cell In ws.Columns(3).Cells
          If IsEmpty(cell) = True Then cell.Select: Exit For
      Next cell
   Selection.PasteSpecial Paste:=xlPasteValues, Transpose:=True
  
  ' Selection.PasteSpecial Paste:=xlPasteValues, Transpose:=True
      wbk.Close savechanges:=False
      Filename = Dir
  Loop
  End Sub
  </code></pre>
  
  <p>Thanks in Advance</p>