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:

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top