Copying data from one sheet to a specific range of cells in a different workbook

[Regi_K]

I have a problem with some scripting I am hoping someone is able to help me with.

I have 2 workbooks, one is a master database which gets its data from the other workbook.

Data needs to be copied from a number of different cells and pasted into a single row in the master database.
I want to be able to do this when I click a button on the data sheet.

I want Cells - D9, E12, R18, K12, from the data sheet to be copied to the master database.

These need to go into the following order on each row;

Column A = R18
Column B = K12
Column E = D9
Column F = E12

I am able to get the data copied across from the data sheet to the master database only in one row.

I am using the following code:

[vba]Dim wbD As Workbook ' where my data is
Dim wbM As Workbook ' where my data wants to go

Set wbD = Workbooks("data.xls") ' Data
Set wbM = Workbooks("master.xls") ' Master Database

With wbD.Worksheets("sheet 1")
.Range("D9").Copy
wbM.Worksheets("sheet 1").Range("E3").PasteSpecial Paste:=xlPasteAll
.Range("E12").Copy
wbM.Worksheets("sheet 1").Range("F3").PasteSpecial Paste:=xlPasteAll
.Range("K12").Copy
wbM.Worksheets("sheet 1").Range("B3").PasteSpecial Paste:=xlPasteAll
.Range("R18").Copy
wbM.Worksheets("sheet 1").Range("A3").PasteSpecial Paste:=xlPasteAll

End With
End Sub[/vba]

The problem is that the row in the master database that is being populated is written over when the script is run again!!....

I want to be able to continue on to the next field/row in the master database if it already has a record rather than writing over the existing record when the script is run again(by clicking on the button in the data sheet).

Is there anyway I can check to see if the row is populated on the master database and if it is to get the script to move onto next row before pasting the data it has copied from the data sheet?

I also would like to automatically open the Master database which is saved on a drive on a separate PC and close it after the data is copied across

At the moment both the data sheet and master database need to be open for the script to work.

Is it possible to get it to work with only the data sheet open?

Can anyone please help me with this problem?

Regi

[Bob Phillips]

[vba]

Dim wbD As Workbook ' where my data is
Dim wbM As Workbook ' where my data wants to go
Dim wsM As Worksheet
Dim lastRow As Long

Set wbD = Workbooks("data.xls") ' Data
Set wbM = Workbooks("master.xls") ' Master Database

Set wsM = wbM.Worksheets("sheet 1")
lastRow = wsM.Cells(wsM.Rows.Count, "A").End(xlUp).Row

With wbD.Worksheets("sheet 1")

.Range("R18").Copy
wsM.Cells(lastRow + 1, "A").PasteSpecial Paste:=xlPasteAll
.Range("K12").Copy
wsM.Cells(lastRow + 1, "B").PasteSpecial Paste:=xlPasteAll
.Range("D9").Copy
wsM.Cells(lastRow + 1, "E").PasteSpecial Paste:=xlPasteAll
.Range("E12").Copy
wsM.Cells(lastRow + 1, "F").PasteSpecial Paste:=xlPasteAll
End With
[/vba]