copying and pasting data from one workbook to another

[Patriot2879]

Hi, i Have the code below where i want to copy data from "!UPDATEDnew!JM on day (sheet Northants)"and paste into "LATE JM on day (sheet Northants & Bucks)", but the code below isnt working and all it does is close my spreadsheets down. Please can you help me please?

Private Sub CommandButton7_Click()
Dim x As Workbook
Dim y As Workbook
Dim vals As Variant

'## Open both workbooks first:
Set x = Workbooks.Open("C:\Users\s21375\Desktop\VBA stuff\!UPDATEDnew!JM on day.xlsm")
Set y = Workbooks.Open("C:\Users\s21375\Desktop\VBA stuff\LATE JM on day.xlsm")

'Store the value in a variable:
vals = x.Sheets("Northants").Range("A1").Value

y.Sheets("Northants & Bucks").Range("A1").Value = vals


End Sub

 

[Fluff]

Did you close both files?

 

[Patriot2879]

i only closed the one (LATE JM on the day) wchih is without the command button i didnt close the other as i wouldnt be able to press the command button

 

[Patriot2879]

hi i just tried something else. i opened up a blank excel file and added a command button with the code and it worked, but it only copied across the one line A29:J29 but it didn't copy down. is there any way it can do this and is there any way i can have the button on my main excel spreadsheet which i copy from?

 

[Fluff]

Try

Private Sub CommandButton2_Click()
Dim x As Workbook
Dim y As Workbook
Dim vals As Variant

Set x = ThisWorkbook
Set y = Workbooks.Open("C:\Users\s21375\Desktop\VBA stuff\LATE JM on day.xlsm")

With x.Sheets("Northants")
   Intersect(.UsedRange, .Range("29:" & .Rows.Count)).Copy y.Sheets("Northants & Bucks").Range("A1")
End With

End Sub

 

[Patriot2879]

Yes that works thank you so much, sorry for the long hall in this, it works great now thank you for your patience

 

[Fluff]

Glad to help & thanks for the feedback