Copying Data to Another Workbook

pwill

Active Member
Joined
Nov 22, 2015
Messages
406
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
ABCDEFG

ABCDEFG

ABCDEFG

ABCDEFG

ABCDEFG

ABCDEFG

ABCDEFG
2ABCDEFG

ABCDEFG

ABCDEFG

ABCDEFG

ABCDEFG

ABCDEFG

ABCDEFG
3ABCDEFG

ABCDEFG

ABCDEFG

ABCDEFG

ABCDEFG

ABCDEFG

ABCDEFG
4ABCDEFG

ABCDEFG

ABCDEFG

ABCDEFG

ABCDEFG

ABCDEFG

ABCDEFG

<tbody>
</tbody>
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
ABCDEFG

ABCDEFG

ABCDEFG

ABCDEFG

ABCDEFG

ABCDEFG

ABCDEFG
6
ABCDEFG

ABCDEFG

ABCDEFG

ABCDEFG

ABCDEFG

ABCDEFG

ABCDEFG
7
ABCDEFG

ABCDEFG

ABCDEFG

ABCDEFG

ABCDEFG

ABCDEFG

ABCDEFG
8
ABCDEFG

ABCDEFG

ABCDEFG

ABCDEFG

ABCDEFG

ABCDEFG

ABCDEFG
9
ABCDEFG

ABCDEFG

ABCDEFG

ABCDEFG

ABCDEFG

ABCDEFG

ABCDEFG

<tbody>
</tbody>


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

Any help would be aprieciated

Regards

pwill
 
Last edited:

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
8,611
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
 

pwill

Active Member
Joined
Nov 22, 2015
Messages
406
Thanks mumps I will give this a try and let you know how I get on

regards
pwill
 

pwill

Active Member
Joined
Nov 22, 2015
Messages
406
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
 

Forum statistics

Threads
1,085,519
Messages
5,384,157
Members
401,884
Latest member
yashmailbox

Some videos you may like

This Week's Hot Topics

Top