VBA – How to copy row in Excel from one workbook to another?

Task expected:

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.

What I want my VBA code to do is:

  1. Copy row 1 (or specifically cells A1 to A4) of Workbook ‘A’ into Range variable ‘myRange’
  2. Create a new workbook, let’s call this one Workbook ‘B’
  3. Give Workbook ‘B’s default “sheet1” a new name to “Test Name”
  4. Select Workbook ‘B’
  5. Paste ‘myRange’ into first row of ‘Workbook B’
  6. Save ‘Workbook B’ with name “Test Book” and a timestamp enclosed in square brackets. The file must also be of the file extension “xls”
  7. Close ‘Workbook B’ and return to ‘Workbook A’