Macros

[Guest]

Hello,

How do I make a macro that copies data from one spreadsheet and then pastes
it into another spreadsheet, BUT does not paste over the top of the existing
data and pastes a row lower?

Thanks

 

[Norman Jones]

Hi Burg,

The following copies A16 on Sheet1 to a range on Sheet2 starting at the
next empty cell in column A.

Sub Demo()
Dim srcRng As Range, destRng As Range

Set srcRng = Sheets("Sheet1").Range("A16")
Set destRng = Sheets("Sheet2"). _
Cells(Rows.Count, "A").End(xlUp)(2)

srcRng.Copy destRng
End Sub

 

[Guest]

Thanks Norman,

Just wondering how you specify a file path. I want to paste the data into
another spreadsheet. It is in the same folder so does this make it easier?

Thanks

 

[Norman Jones]

Hi Burg,

Try something like:

Sub Demo02()
Dim srcRng As Range, destRng As Range
Dim WB1 As Workbook, WB2 As Workbook

Set WB1 = Workbooks("MySourceBook.xls")
Set WB2 = Workbooks("MyDestinationBook.xls")
Set srcRng = WB1.Sheets("Sheet1").Range("A16")
Set destRng = WB2.Sheets("Sheet1"). _
Cells(Rows.Count, "A").End(xlUp)(2)

srcRng.Copy destRng
End Sub

 

[Guest]

Perfect. Thanks

Hi Burg,

Try something like:

Sub Demo02()
Dim srcRng As Range, destRng As Range
Dim WB1 As Workbook, WB2 As Workbook

Set WB1 = Workbooks("MySourceBook.xls")
Set WB2 = Workbooks("MyDestinationBook.xls")
Set srcRng = WB1.Sheets("Sheet1").Range("A16")
Set destRng = WB2.Sheets("Sheet1"). _
Cells(Rows.Count, "A").End(xlUp)(2)

srcRng.Copy destRng
End Sub