Results 1 to 5 of 5

Thread: Copying Data to Another Workbook

  1. #1
    Board Regular
    Join Date
    Nov 2015
    Posts
    404
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Copying Data to Another Workbook

    Hi can anyone help with a macro to copy a range of data from one workbook to another?

    I have rows of data on workbook1 sheet(Data) range A2:H that I want to copy to workbook2 sheet(Data).
    I need the data to copy to workbook2 in the same columns as workbook1 but continue the data to the next rows from where the last data was copied.

    Column A has number count for each set of data with empty cells between each set of data, this is where I am struggling to find the last empty row to continue the data.

    ie workbook2 has the following data A2:H29

    1
    A B C D E F G

    A B C D E F G

    A B C D E F G

    A B C D E F G

    A B C D E F G

    A B C D E F G

    A B C D E F G
    2 A B C D E F G

    A B C D E F G

    A B C D E F G

    A B C D E F G

    A B C D E F G

    A B C D E F G

    A B C D E F G
    3 A B C D E F G

    A B C D E F G

    A B C D E F G

    A B C D E F G

    A B C D E F G

    A B C D E F G

    A B C D E F G
    4 A B C D E F G

    A B C D E F G

    A B C D E F G

    A B C D E F G

    A B C D E F G

    A B C D E F G

    A B C D E F G
    I want to use a macro to copy the next set of data from workbook1 to continue the data on workbook2

    ie workbook1 has the following data A2:H36

    5
    A B C D E F G

    A B C D E F G

    A B C D E F G

    A B C D E F G

    A B C D E F G

    A B C D E F G

    A B C D E F G
    6 A B C D E F G

    A B C D E F G

    A B C D E F G

    A B C D E F G

    A B C D E F G

    A B C D E F G

    A B C D E F G
    7 A B C D E F G

    A B C D E F G

    A B C D E F G

    A B C D E F G

    A B C D E F G

    A B C D E F G

    A B C D E F G
    8 A B C D E F G

    A B C D E F G

    A B C D E F G

    A B C D E F G

    A B C D E F G

    A B C D E F G

    A B C D E F G
    9 A B C D E F G

    A B C D E F G

    A B C D E F G

    A B C D E F G

    A B C D E F G

    A B C D E F G

    A B C D E F G


    So the copied range would continue on workbook2 from A30:H

    Any help would be aprieciated

    Regards

    pwill
    Last edited by pwill; Sep 10th, 2017 at 12:50 PM.

  2. #2
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    8,299
    Post Thanks / Like
    Mentioned
    95 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Copying Data to Another Workbook

    Assuming both workbooks are open, place the following macro in a regular module in Book1 and save the workbook as a macro-enabled file. Change the workbook names in the code to match your actual names including the file extension. Also change the sheet names to suit your needs if necessary.
    Code:
    Sub CopyData()
        Application.ScreenUpdating = False
        Dim bottomB1 As Long
        bottomB1 = Workbooks("Book1.xlsm").Sheets("Sheet1").Range("B" & Rows.Count).End(xlUp).Row
        Dim bottomB2 As Long
        bottomB2 = Workbooks("Book2.xlsx").Sheets("Sheet1").Range("B" & Rows.Count).End(xlUp).Row
        Workbooks("Book1.xlsm").Sheets("Sheet1").Range("A1:H" & bottomB1).Copy Workbooks("Book2.xlsx").Sheets("Sheet1").Cells(bottomB2 + 1, 1)
        Application.ScreenUpdating = True
    End Sub

  3. #3
    Board Regular
    Join Date
    Nov 2015
    Posts
    404
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copying Data to Another Workbook

    Thanks mumps I will give this a try and let you know how I get on

    regards
    pwill

  4. #4
    Board Regular
    Join Date
    Nov 2015
    Posts
    404
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copying Data to Another Workbook

    Quote Originally Posted by mumps View Post
    Assuming both workbooks are open, place the following macro in a regular module in Book1 and save the workbook as a macro-enabled file. Change the workbook names in the code to match your actual names including the file extension. Also change the sheet names to suit your needs if necessary.
    Code:
    Sub CopyData()
        Application.ScreenUpdating = False
        Dim bottomB1 As Long
        bottomB1 = Workbooks("Book1.xlsm").Sheets("Sheet1").Range("B" & Rows.Count).End(xlUp).Row
        Dim bottomB2 As Long
        bottomB2 = Workbooks("Book2.xlsx").Sheets("Sheet1").Range("B" & Rows.Count).End(xlUp).Row
        Workbooks("Book1.xlsm").Sheets("Sheet1").Range("A1:H" & bottomB1).Copy Workbooks("Book2.xlsx").Sheets("Sheet1").Cells(bottomB2 + 1, 1)
        Application.ScreenUpdating = True
    End Sub


    Thanks mumps

    After changing sheet names etc.. your code works perfect

    regards
    pwill

  5. #5
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    8,299
    Post Thanks / Like
    Mentioned
    95 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Copying Data to Another Workbook

    You are very welcome.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •