title: Consolidate values from multiple workbook to one workbook
body: <p>I've got multiple excel files (.xlsm), which I would like to consolidate into 1 different workbook (just specific range). The range will be always the same, which means that I need to loop through the files in specific folder / folders and copy the range and paste as values into the new workbook.</p>
  <p>I've written a script, which I thought that could work, but it does not. It gives me an error message:
  <a href="https://i.stack.imgur.com/8M5fz.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/8M5fz.png" alt="enter image description here" /></a></p>
  <p>Could you advise me what's wrong, please? It gives me the error on this line</p>
  <pre><code>x = Sheets(&quot;DBC PGB Review&quot;).Range(&quot;B3:E3&quot;).Copy
  </code></pre>
  <p>Or am I completely on a wrong way?</p>
  <pre><code>Sub LoopDBCs()
  
  Dim myfolder As String
  Dim myfile As String
  Dim wb As Workbook, ws As Worksheet
  Set wb = ThisWorkbook
  Set ws = wb.Sheets(&quot;DBCs&quot;)
  Dim i As Integer
  
  Dim x As Integer
  Dim y As Integer
  
  LastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
  
  myfolder = &quot;F:\REQUIREMENTS\EXCEL\Retrieve DBC Data\DBCs\&quot;
  myfile = Dir(myfolder &amp; &quot;*.xlsm&quot;)
  
  i = 2
  
  Do While myfile &lt;&gt; &quot;&quot;
      Workbooks.Open Filename:=myfolder &amp; myfile, UpdateLinks:=0
          x = Sheets(&quot;DBC PGB Review&quot;).Range(&quot;B3:E3&quot;).Copy
      ActiveWorkbook.Close savechanges:=False
  
      ws.Activate
      ws.Range(&quot;A:D&quot; &amp; LastRow + 1).PasteSpecial xlPasteValues
      
      i = i + 1
  
      myfile = Dir
  Loop
  
  End Sub
  </code></pre>
  <p>Many thanks!</p>