VBA code: copy data from sheet and paste values onto another specidied workbook

[Jasongriff]

Good Afternoon all (or good morning depending where you are (I'm working on GMT)),


I’m after somehelp regarding some VBA code I am trying to put together, I believe the variousaspects are feasible but I don’t have the knowledge of how to putit all together; I’m hoping someone can help me here.


Therequirements of the macro are as follows:



The macro is triggered every time the file “x” is saved by a user (multiple users will have access to this file)
Upon trigger, the 1stsheet of x, called “Summary” is copied and the values are pasted into another pre-existing file “y” on a specified sheet “x summary” - this should overwrite the information already on the “x summary” sheet every time it is saved.


Any ideas / solutions are welcome.

Thanks

[snb]

The best idea is to upload a sample workbook in this forum.

[Jasongriff]

Thanks for the reply - I don't have a file with the information to hand (I wanted to know the concept was sound before I built it).

However I have attached 2 files to give an outline. I want a macro to copy and paste values: source data from the summary sheet from workbook x - destination x sheet on workbook y

[paulked]

You say this is going to be shared, if this is on OneDrive then this probably won't work!

The x.xlsm and y.xlsx files have to be in the same location.

Add a sheet called Temp.

In the ThisWorkBook module


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
SendData
End Sub

In a Code module


Sub SendData()
Dim mb As Workbook, rng As Range
Application.ScreenUpdating = False
With Sheets("Summary")
.Range("A1:C8").Copy Sheets("Temp").Cells(1, 1)
For Each rng In .Range("A1:C8")
If rng.HasFormula Then rng.Formula = rng.Value
Next
End With
Set mb = Workbooks.Open(ThisWorkbook.Path & "\y.xlsx")
With mb
ThisWorkbook.Sheets("Summary").Range("A1:C8").Copy .Sheets("x").Cells(1, 1)
.Save
.Close
End With
Sheets("Temp").Range("A1:C8").Copy Sheets("Summary").Cells(1, 1)
End Sub

[Jasongriff]

Thanks for the reply.

The file is intended to be in sharepoint (with both files in the same folder location) - I'm hoping this will be okay?

I have tried the code but it doesn't appear to work, upon saving file x there are no issues or bugs, but file y does not receive any data.

[paulked]

They have to be in the same directory and either make sure the y file is .xlsx or change the code if it is .xlsm

[paulked]

Not sure about SharePoint, I've never used it sorry.

[Jasongriff]

Right you are! Thank you so much for the help...

...here's hoping it works in sharepoint *run's off to go and test it*

[paulked]

You're welcome and good luck! :beerchug: