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

 

[Patriot2879]

HI any ideas on this at all please or do you need more information from me? thanks for your time

 

[Fluff]

How about

x.Sheets("Northants").UsedRange.Copy y.Sheets("Northants & Bucks").Range("A1")

 

[Patriot2879]

Hi thanks for the reply how do i put this in the rest of my code please?
I deleted my old code

vals = x.Sheets("Northants").Range("A1").Value

and replaced it with yours but this didnt work, it refreshed and deleted the whole code when refreshed and didnt move the data to other spreadsheet. Hope you can help

 

[jim may]

Replace your original 2 lines:
vals = x.Sheets("Northants").Range("A1").Value

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

With the recommended 1 line provided.

HTH...

 

[Patriot2879]

sorry i just done that and still the same it deletes whatever text is in A1 and doesnt paste into the other folder, it also deletes the code

 

[Fluff]

Do you mean that it deletes the data on the "Northants" sheet?
There is nothing in the code you supplied, or my mod that would delete the code.

 

[Patriot2879]

Hi yes when i add the code to the Command button and run it, the little wheel spins around then the workbook refreshes and no data has been copied across and the code gets deleted from the command button

 

[Fluff]

Did you save the workbook before running the code?

 

[Patriot2879]

hi i just tried again and it worked, but now i have just changed the folder name to 'Northants JM on day' and now it is not working again, i would like it to collect the data from 'Northants JM on day' from line A30 across to J30 then down to the last entry, can you help me again please thanks again for everything you have helped with

Private Sub CommandButton2_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\Northants JM on day.xlsm")
Set y = Workbooks.Open("C:\Users\s21375\Desktop\VBA stuff\LATE JM on day.xlsm")

x.Sheets("Northants").UsedRange.Copy y.Sheets("Northants & Bucks").Range("A30")

End Sub