Command button to send data to another workbook

**alfgrey** · 02-22-2014, 02:14 PM

The data in 4 specific cells in C:\Users\Office\Dropbox\WB1 needs to (upon clicking command button) be copied to the next available blank cell in 4 specefic rows in C:\users\Office\Dropbox\WB2.

WB1 Specific Cells List
Sheet1!A4
Sheet1!Q7
Sheet1!N22
Sheet2!E3

Data from these cells are to be copied to the rows listed below respectively.

WB2 Destination Rows (Next available blank row)
Sheet1! Row B
Sheet1! Row D
Sheet1! Row F
Sheet1! Row J

I gather from reading other posts that sending this data within the same WB is fairly easy, is it easy to do between WB's though as i must have seperate WB's? Im a novice with all this so any advice is greatly received.!

**john55** · 02-23-2014, 03:28 AM

hi, try this and see if it helps you

Sub Button4_Click()
Dim x As Workbook
Dim y As Workbook
Application.ScreenUpdating = 0

Set x = Workbooks("WB1") 'copy data from
Set y = Workbooks.Open("C:\users\Office\Dropbox\WB2") 'open ext file to paste data

x.Sheets("Sheet1").Range("A4").Copy
y.Sheets("Sheet1").Range("b" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial

x.Sheets("Sheet1").Range("Q7").Copy
y.Sheets("Sheet1").Range("d" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial

x.Sheets("Sheet1").Range("N22").Copy
y.Sheets("Sheet1").Range("f" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial

x.Sheets("Sheet1").Range("E3").Copy
y.Sheets("Sheet1").Range("j" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial


'Close and save y:
y.Close SaveChanges:=True
Application.ScreenUpdating = 1
Application.CutCopyMode = 0
End Sub