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]

If you've changed the name of the folder containing the file, you'll need to change the file path in the code.

 

[Patriot2879]

i did that

 

[Fluff]

What exactly does

and now it is not working again

mean?

 

[Patriot2879]

Hi sorry, i did get it working but now its not i don't know what i have done wrong i am still a beginner in this and i thank you for your patience, below is the code i have used and the only things i have changed is the main file name to 'Northants JM on day.xlsm' and where i want the data copied from which i have changed to A29. but i want this pasted into A1 on the ' LATE JM on day.xlsm, sheet 'Northants & Bucks'

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

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("A29")

End Sub

 

[Fluff]

How about

x.Sheets("Northants").Range("A29:J29").Copy y.Sheets("Northants & Bucks").Range("A1")

 

[Patriot2879]

nope i really don't know what i am doing wrong , i want to copy from Northants JM on the day sheet 'Northants' from cell A29:J29 then go down until last row, and paste this information into Late 'JM On Day' into sheet 'Northants' cell A1:J1 then go down until last row, sorry about all this, its probably something i am doing my end,

 

[Fluff]

Is anything getting copied across?

 

[Patriot2879]

hi, no nothing at all all it does is re-opens the Northants JM on the day.xlsm, i have saved both files and had them open when i click on the commandbutton

 

[Fluff]

Either the files need to be closed when you run the code, or you need to change the code so that you are not opening them.

 

[Patriot2879]

i closed the LATE JM on day.xlsm and pressed the command button on the Northants JM of the day and still no joy , do i not need to pust a .Paste code in anywhere?