title: copying from closed workbook excel VBA
body: <p>Ok I get to the point where code is reading data from closed workbook and can paste it into sheet2 in that workbook. This is my new code:</p>
  
  <pre><code>    Sub Copy456()
  
      Dim iCol As Long
      Dim iSht As Long
      Dim i As Long
  
  
  
      'Fpath = "C:\testy" ' change to your directory
      'Fname = Dir(Fpath &amp; "*.xlsx")
  
      Workbooks.Open ("run1.xlsx")
  
      For i = 1 To Worksheets.Count
          Worksheets(i).Activate
  
       ' Loop through columns
       For iSht = 1 To 6 ' no of sheets
       For iCol = 1 To 6 ' no of columns
  
          With Worksheets(i).Columns(iCol)
  
              If ((.Cells(1, 1).Value = "Time")) Then ' if first cell=Time then copy two columns
                  Range(.Cells(1, 2), .End(xlDown)).Select
                  Selection.Copy Destination:=Workbooks("Your Idea.xlsm").Worksheets("Sheet2").Columns((i + 1) + i).Cells(2, 1)
                  Worksheets("Sheet2").Cells(i * 2 + 1) = Worksheets(i).Name
              Else
                  ' do nothing
  
              End If
          End With
  
      Next iCol
      Next iSht
  Next i
  End Sub
  </code></pre>
  
  <p>But once I change that part of code:</p>
  
  <pre><code>            Selection.Copy Destination:=Worksheets("Sheet2").Columns((i + 1) + i).Cells(2, 1)
  </code></pre>
  
  <p>into that code:</p>
  
  <pre><code>   Destination:=Workbooks("general.xlsx").Worksheets("Sheet2").Columns((i + 1) + i).Cells(2, 1)
  </code></pre>
  
  <p>It stop working issuing error: "subscription is out of range". 
  File general.xlsx is an empty file which is closed as well.</p>
  
  <p>When I change code into:</p>
  
  <pre><code>`Selection.Copy Destination:=Workbooks("Your Idea.xlsm").Worksheets("Sheet2").Columns((i + 1) + i).Cells(2, 1)
  </code></pre>
  
  <p>It then issue an error: "1004 cannot change part of merged cell". 
  File "Your Idea.xlsm" is the file from which I running this script.</p>
  
  <p>Any help with this problem?</p>