Consulting

Results 1 to 9 of 9

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

  1. #1

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

    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:


    1. The macro is triggered every time the file “x” is saved by a user (multiple users will have access to this file)
    2. 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

  2. #2
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    The best idea is to upload a sample workbook in this forum.

  3. #3
    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
    Attached Files Attached Files

  4. #4
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,007
    Location
    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
    Attached Files Attached Files
    Semper in excretia sumus; solum profundum variat.

  5. #5
    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.

  6. #6
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,007
    Location
    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
    Semper in excretia sumus; solum profundum variat.

  7. #7
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,007
    Location
    Not sure about SharePoint, I've never used it sorry.
    Semper in excretia sumus; solum profundum variat.

  8. #8
    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*

  9. #9
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,007
    Location
    You're welcome and good luck!
    Semper in excretia sumus; solum profundum variat.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •