title: VBA - How to copy row in Excel from one workbook to another?
body: <p>Despite many posts I have looked through being of along the same lines as my question, none of the answers satisfy what I am looking for. If you can link me to one I'd gladly read it.</p>
  
  <p>I have a workbook with worksheets. For simplicity, let's say my workbook has a worksheet. And in my worksheet which is called "Sheet1", there is data in cells A1 to A4.</p>
  
  <p>What I want my VBA code to do is:</p>
  
  <ol>
  <li>Copy row 1 (or specifically cells A1 to A4) of Workbook 'A' into Range variable 'myRange'</li>
  <li>Create a new workbook, let's call this one Workbook 'B'</li>
  <li>Give Workbook 'B's default "sheet1" a new name to "Test Name"</li>
  <li>Open Workbook 'B' (though I realise that VBA code "Workbooks.Add" opens a new book so this step may be redundant since Workbooks.Add covers half of point 2 and 3)</li>
  <li>Paste 'myRange' into first row of 'Workbook B'</li>
  <li>Save 'Workbook B' with name "Test Book" and a timestamp enclosed in square brackets. The file must also be of the file extension "xls"</li>
  <li>Close 'Workbook B' and return to 'Workbook A'</li>
  </ol>
  
  <p>What I have so far is this:</p>
  
  <pre><code>Sub OpenAndSaveNewBook()
      'Declarations
      Dim MyBook As String
      Dim MyRange As Range
      Dim newBook As Workbook
  
      'Get name of current wb
      MyBook = ThisWorkbook.Name
      Set MyRange = MyBook.Sheets("Sheet1").Range("A1,F1")
  
      'Create/Open new wb
      newBook = Workbooks.Add
  
      'Save new wb with XLS extension
      ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path &amp; "/" &amp; "TEST-BOOK", _
                              FileFormat:=xlNormal, CreateBackup:=False
  
      '===NOTE: BEFORE THE FOLLOWING RUNS I NEED TO PERFORM ACTIONS ON CELLS VIA VBA ON
      '===WORKBOOK 'A'. DOES THE NEWLY CREATE WORKBOOK BECOME THE PRIMARY/ACTIVE WORKBOOK
      '===? AND SO THEN DO I NEED TO ACTIVATE WORKBOOK 'A'? 
      ActiveWorkbook.Close savechanges:=True
  
      'Return focus to workbook 'a'
      MyBook.Activate
  End Sub
  </code></pre>
  
  <p>As you can see, I am lacking the code that will handle:</p>
  
  <ul>
  <li>the pasting of my copied data to the new workbook</li>
  <li>the changing of the new workbook's sheet1 name to something else</li>
  <li>adding a timestamp to the filename string on save</li>
  </ul>
  
  <p>Lastly, I have included a question in my code as I think I may have a misunderstanding of the ActiveWorkbook method. AFAIK when the code "Workbooks.Add" runs this becomes the Active Workbook, i.e. one with the focus. Does this effect how the VBA code running on Workbook 'A'? Does this mean that if I wanted to add code to manipulate cells of Workbook 'A' then I would need to use "MyBook.Activate" where 'MyBook' holds the string of Workbook 'A's actual title?</p>
  
  <p>Any help will be greatly appreciated.</p>
  
  <p>Thanks,
  QF</p>