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

Answer 2

Avoid references to ActiveWorkbook in favour of explicit references wherever possible.

As you've found, it can be confusing knowing what's currently active, and you do not need to activate a workbook to manipulate it.

So you should be using

newBook.SaveAs... 
newBook.Close...

Recorded macros tend to activate workbooks in order to work on them, but that's because that's the way a human who recorded them works! All activation really does is change focus.

The same applies to making selections and then manipulating the current selection; it's not necessary in VBA and tends to be slower than direct manipulation.

Answer 3

The awesome thing about Excel is the 'Record Macro' function. I started recording a macro and just followed the steps you outlined, then made a few minor modifications to the code that Excel provided as the recorded macro:

Range("A1:F1").Select
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
Sheets("Sheet1").Name = "Test Name"
Application.CutCopyMode = False
myNewFileName = myPath & myTestName & "_" & Date & ".xls"
ActiveWorkbook.SaveAs Filename:=myNewFileName _
    , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

The Date function returns the current system date. It is important to note that the square brackets that you wanted are not valid filename characters; Excel will throw an error if you try to use those.

Answer 4

Turn the macro-recorders on; carefully execute the steps you want; stop the recorder; "edit" the macro generated. Fix as you need to make the program you intend, e.g., to parameterize it.