copying and pasting data from one workbook to another

Patriot2879

Well-known Member
Joined
Feb 1, 2018
Messages
1,259
Office Version
  1. 2010
Platform
  1. Windows
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?

Code:
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
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
HI any ideas on this at all please or do you need more information from me? thanks for your time :)
 
Upvote 0
How about
Code:
x.Sheets("Northants").UsedRange.Copy y.Sheets("Northants & Bucks").Range("A1")
 
Upvote 0
Hi thanks for the reply how do i put this in the rest of my code please?
I deleted my old code
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 :)
 
Upvote 0
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...
 
Upvote 0
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 :(
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
Did you save the workbook before running the code?
 
Upvote 0
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

Code:
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
 
Upvote 0

Forum statistics

Threads
1,224,828
Messages
6,181,217
Members
453,024
Latest member
Wingit77

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top