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

Question

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.

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. 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)
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'

What I have so far is this:

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 & "/" & "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

As you can see, I am lacking the code that will handle:

• the pasting of my copied data to the new workbook
• the changing of the new workbook's sheet1 name to something else
• adding a timestamp to the filename string on save

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?

Any help will be greatly appreciated.

Thanks, QF

Answer 1

Instead of copy pasting the way you mentioned above, you can directly do this. This will also negate the use of a variable.

MyBook.Sheets("Sheet1").Rows("1:4").copy _
newBook.Sheets("Sheet1").Rows("1")

EDIT

I just noticed an error with your code.

newBook = Workbooks.Add

This line will give you an error as you have to use Set

Your code can be written as

Option Explicit

Sub OpenAndSaveNewBook()
    Dim MyBook As Workbook, newBook As Workbook
    Dim FileNm As String

    Set MyBook = ThisWorkbook

    FileNm = ThisWorkbook.Path & "\" & "TEST-BOOK.xls"
    Set newBook = Workbooks.Add

    With newBook
        MyBook.Sheets("Sheet1").Rows("1:4").Copy .Sheets("Sheet1").Rows("1")

        'Save new wb with XLS extension
        .SaveAs Filename:=FileNm, FileFormat:=xlNormal, CreateBackup:=False

        .Close Savechanges:=False
    End With
End Sub

MORE EDIT

Elaborating on the use of SET

I would recommend you to see this post.

LINK: Worksheets does not work