title: Dynamic Worksheet search
body: <p>I have created some coding which is designed to open up a file from a designated filepath, search for a worksheet and copy the desired information from the worksheets I am looking for and paste into the original document which the coding is assigned to. The worksheet I need to copy from is named MMM YY. When I originally set the coding up, I would manually update monthly, however, I am passing the document onto someone else to use, so would like the search reference to be dynamic. I’ve created the below coding where ‘DRT’ is the dynamic reference, where <code>DRT = Today()</code> formatted in the MMM YY style. </p>
  
  <p>Many Thanks</p>
  
  <pre><code>Option Explicit
  
  Sub Macro1()
  
  Dim ws As Worksheet
  Dim Y As Workbook
  Dim X As Workbook
  Dim Filepath As String
  Dim DRT As String
  
  Filepath = ThisWorkbook.Worksheets("Calc").Range("Filepathdan").Value
  DRT = ThisWorkbook.Worksheets("Calc").Range("DR").Value
  
  Set Y = Application.ActiveWorkbook
  Set X = Application.Workbooks.Open(Filename:=Filepath)
  
  For Each ws In X.Sheets
      If InStr(1, ws.Name, DRT) Then
          Cells.Copy
          Y.Activate
          Worksheets("Dan").Range("A1").PasteSpecial (xlPasteValues)
          Worksheets("Dan").Range("A1").PasteSpecial (xlPasteFormats)
          Exit For
      End If
  Next ws
  X.Close False
  
  End Sub
  </code></pre>