Page 2 of 2 FirstFirst 12
Results 11 to 17 of 17

Thread: Copy/Paste from one workbook to another 2 workbooks with range
Thanks Thanks: 0 Likes Likes: 0

  1. #11
    Board Regular
    Join Date
    Feb 2010
    Location
    London, UK
    Posts
    9,287
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Copy/Paste from one workbook to another 2 workbooks with range

    Is the destination sheet named Sheet1 in the workbook that is open?

    If this gives the same error, without your files difficult to diagnose:
    Code:
    Private Sub CommandButton1_Click()
                
        Dim r As Range: Set r = ThisWorkbook.Sheets("Sheet1").Range("A1:E2")
        
        UpdateWorkbook "C:\Users\s21375\Desktop\test\Book16.xlsx", r
        UpdateWorkbook "C:\Users\s21375\Desktop\test3\Book18.xlsx", r
        
        Set r = Nothing
        
    End Sub
    
    Private Sub UpdateWorkbook(ByRef sBook As String, ByRef r As Range)
    
        Workbooks.Open sBook
        With ActiveWorkbook
            .Sheets("Sheet1").Cells(1, 1).Resize(r.Rows, r.Columns).Value = r.Value
            .Close True
        End With
        
    End Sub
    Last edited by JackDanIce; Sep 5th, 2019 at 08:05 AM.


  2. #12
    Board Regular
    Join Date
    Feb 2018
    Location
    Midlands, UK
    Posts
    912
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy/Paste from one workbook to another 2 workbooks with range

    hi yes the sheet name for book 16 and 18 is called Sheet1, I have just tried the code again with all the workbooks open then closed and still getting the same error. hope you can help.

  3. #13
    Board Regular
    Join Date
    Feb 2018
    Location
    Midlands, UK
    Posts
    912
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy/Paste from one workbook to another 2 workbooks with range

    Hi I have got the code below to work but for only book18 how do I add the other book? book16? please
    Code:
    Private Sub CommandButton1_Click()
     
        Dim srcWB As Workbook
        Dim destWB As Workbook
        Dim fName As String
        Dim lastRow As Long
     
    '   Capture current workbook as source workbook
        Set srcWB = ActiveWorkbook
     
    '   Open destination workbook and capture it as destination workbook
        Workbooks.Open "C:\Users\s21375\Desktop\test3\Book18.xlsx"
        Set destWB = ActiveWorkbook
      
      
    '   Copy data from source workbook to destination workbook
    srcWB.Sheets("Sheet1").Range("A1:E2").Copy
        destWB.Sheets("Sheet1").Range("A1").PasteSpecial xlPasteValues
    '   Save changes and close destination workbook
        destWB.Close SaveChanges:=True
    End Sub

  4. #14
    Board Regular
    Join Date
    Feb 2010
    Location
    London, UK
    Posts
    9,287
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Copy/Paste from one workbook to another 2 workbooks with range

    If this gives the same error, without your files difficult to diagnose

    It works for me when I mock some test files up. Without your files, I can't determine the cause of error.


  5. #15
    Board Regular
    Join Date
    Feb 2018
    Location
    Midlands, UK
    Posts
    912
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy/Paste from one workbook to another 2 workbooks with range

    Hi I have managed to go get the code working in a test mock up as well, but when Iuse the code in my actual button I keep getting errors, on the sheet3, and thisis correct and all the file links are correct as well and the sheet name ‘Sheet3’but its not working I am so confused, I don’t know f it crashes with the othercoding I have? I have attached the file Idon’t know if you can see what I am missing? Test33 isthe main one with the button and I want to paste into test11 and test22.

    https://www.dropbox.com/s/1habya3x1ujdiu5/test33.xlsm?dl=0


    https://www.dropbox.com/s/9al2wq2tb7hd6wn/test11.xlsm?dl=0

    https://www.dropbox.com/s/re4wl3eyb5zzxph/test22.xlsm?dl=0










  6. #16
    Board Regular
    Join Date
    Feb 2018
    Location
    Midlands, UK
    Posts
    912
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy/Paste from one workbook to another 2 workbooks with range

    Hi I hope you can help please? I am really confused to why it is not working.

  7. #17
    Board Regular
    Join Date
    Feb 2010
    Location
    London, UK
    Posts
    9,287
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Copy/Paste from one workbook to another 2 workbooks with range

    The VBA project is locked so can't determine what test33.xlsm is doing nor can interact with the code to see why it doesn't open files.


Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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