title: Dynamic Workbook and Worksheet name in Macro or VBA
body: <p>I have this code that works: (am currently using Excel 2003)</p>
  
  <pre><code>Sub test_copy1()    
  
  Dim wb1 As Workbook     
  Dim wb2 As Workbook     
  Dim ws1 As Worksheet     
  Dim ws2 As Worksheet      
  
  Set wb1 = Workbooks("Workbook2.xls")     
  Set wb2 = ThisWorkbook     
  Set ws1 = wb1.Sheets("Run 1")     
  Set ws2 = wb2.Sheets("Sheet3") 
  
  ws2.Range("A1:h30") = ws1.Range("A177:H206").Value  
  
  End Sub
  </code></pre>
  
  <p>In the current Workbook "Sheet 3"
  Cell J1 = Workbook2.xls
  Cell J2 = Run 1</p>
  
  <p>How can I feed the information from the current Workbook "Sheet 3" into the macro from the cells so that it may be changed as needed and still work?</p>
  
  <p>What I mean is to have 2 cells that can change in a sheet, either by drop down or manually filled in on the current workbook sheet "sheet 3".
  Cell: J1 would equal a file name (Workbook2.xls) and J2 would equal a sheet name (Run 1).</p>
  
  <p>The user would be able to change the file name and sheet name when they needed and then hit a button to process the copy and paste.</p>
  
  <p>I would like the code to take the information from the cells and put them into the code instead of "hard" coding the file name of the workbook and sheet.</p>
  
  <p>I hope that makes more sense</p>
  
  <p>Thank you for any help.</p>
  
  <p>~DA </p>
  
  <p>-----UPDATE----</p>
  
  <p>I have tried this code but it keeps crashing on the first Set Wb1=</p>
  
  <pre><code>    Sub test_copy1()
  
          Dim wb1 As Workbook
          Dim wb2 As Workbook
          Dim ws1 As Worksheet
          Dim ws2 As Worksheet
  
          Set wb1 = Workbooks(ws2.Range("J1"))
          Set wb2 = ThisWorkbook
          Set ws1 = wb1.Sheets(ws2.Range("J2"))
          Set ws2 = wb2.Sheets("Sheet3")
  
          ws2.Range("A1:h30") = ws1.Range("A177:H206").Value
  
      End Sub
  </code></pre>
  
  <p>------Update -------</p>
  
  <p>I got it to work using you all examples and adding .Value after the range :)</p>
  
  <pre><code>Sub test_copy1()
  
      Dim wb1 As Workbook
      Dim wb2 As Workbook
      Dim ws1 As Worksheet
      Dim ws2 As Worksheet
  
      Set wb2 = ThisWorkbook
      Set ws2 = wb2.Sheets("Sheet3")
  
      Set wb1 = Workbooks(ws2.Range("J1").Value)
      Set ws1 = wb1.Sheets(ws2.Range("J2").Value)
  
      ws2.Range("A1:h30") = ws1.Range("A177:H206").Value
  
  End Sub
  </code></pre>
  
  <p>Thank you, thank you for all your help!!</p>