Geek Answers Handbook

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

Despite the many posts I looked through on the same questions as my question, none of the answers satisfy what I'm looking for. If you can connect me with one, I would love to read it.

I have a book with worksheets. For simplicity, suppose there is a worksheet in my book. And on my sheet called "Sheet1", cells A1-A4 have data.

I want my VBA code to execute:

  • Copy line 1 (or, in particular, cells A1-A4) of book "A" to the range variable "myRange"
  • Create a new book, call this book "B"
  • Give the default workbook “B” “sheet1” a new name “Test Name”
  • Open book "B" (although I understand that the VBA code "Workbooks.Add" opens a new book, so this step may be redundant because Workbooks.Add covers half of items 2 and 3).
  • Insert 'myRange' into the first line of "Workbook B"
  • Save Workbook B with the name Test Book and the time stamp enclosed in square brackets. The file must also have the file extension "xls"
  • Close Workbook B and return to Workbook A

What I still know:

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 miss the code that will handle:

  • Paste my copied data into a new book
  • change the name of the new book book1 to another.
  • add timestamp to file name string when saving

, , , -, ActiveWorkbook. AFAIK, "Workbooks.Add", , .. . , VBA "A"? , Workbook 'A', "MyBook.Activate", "MyBook" " ". ?

.

, QF

+5
4

, , , . .

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

.

newBook = Workbooks.Add

, Set

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

Set

.

LINK:

+9

ActiveWorkbook , .

, , , , , .

,

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

, , , , ! .

; VBA , .

+4

Excel - " ". , , Excel :

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

Date . , , , ; Excel , .

+3

Turn on the macro recorders; carefully follow the steps you want; stop the recorder; "modify" the created macro. Correct how you want the program you intend to, for example, to parameterize it.

+1
source

More articles:


All Articles