Results 1 to 6 of 6

Thread: VBA to Copy and Paste From One Workbook to Another
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2019
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA to Copy and Paste From One Workbook to Another

    Hello,

    I have two workbooks, 2018 and 2019. Both have 100+ tabs with different names.

    2018 has data in various cells throughout each tab.

    2019 has the same number of tabs with the same titles as 2018, but is completely blank otherwise.

    I would like to copy multiple cells from 2018 (A4, B18, C86, C88 & D1) and paste them into a different cell (A4 in 2018 gets pasted into A12 in 2019, etc.) in the corresponding tab in the 2019 workbook.

    Any and all help would be much appreciated.

    Thank you.

  2. #2
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    8,298
    Post Thanks / Like
    Mentioned
    95 Post(s)
    Tagged
    5 Thread(s)

    Default Re: VBA to Copy and Paste From One Workbook to Another

    You mentioned where A4 in 2018 gets pasted. What about B18, C86, C88 and D1?
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  3. #3
    New Member
    Join Date
    Aug 2019
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to Copy and Paste From One Workbook to Another

    Sure, B18 get pasted to B89, C86 get pasted to C88 and D1 gets pasted to J18.

    Thank you.

    Quote Originally Posted by mumps View Post
    You mentioned where A4 in 2018 gets pasted. What about B18, C86, C88 and D1?

  4. #4
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    8,298
    Post Thanks / Like
    Mentioned
    95 Post(s)
    Tagged
    5 Thread(s)

    Default Re: VBA to Copy and Paste From One Workbook to Another

    Make sure that both workbooks are open. Place this macro in the "2018" workbook. Change the workbook name in the code (in red) to suit your needs.
    Code:
    Sub CopyCells()
        Application.ScreenUpdating = False
        Dim ws As Worksheet, desWB As Workbook
        Set desWB = Workbooks("2019.xlsx")
        For Each ws In Sheets
            With desWB
                With .Sheets(ws.Name)
                    .Range("A12") = ws.Range("A4")
                    .Range("B89") = ws.Range("B18")
                    .Range("C88") = ws.Range("C8")
                    .Range("J18") = ws.Range("D1")
                End With
            End With
        Next ws
        Application.ScreenUpdating = True
    End Sub
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  5. #5
    New Member
    Join Date
    Aug 2019
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to Copy and Paste From One Workbook to Another

    This is incredible. You saved me hours, and hours, and hours of data entry. Thank you so much!!

    Quote Originally Posted by mumps View Post
    Make sure that both workbooks are open. Place this macro in the "2018" workbook. Change the workbook name in the code (in red) to suit your needs.
    Code:
    Sub CopyCells()
        Application.ScreenUpdating = False
        Dim ws As Worksheet, desWB As Workbook
        Set desWB = Workbooks("2019.xlsx")
        For Each ws In Sheets
            With desWB
                With .Sheets(ws.Name)
                    .Range("A12") = ws.Range("A4")
                    .Range("B89") = ws.Range("B18")
                    .Range("C88") = ws.Range("C8")
                    .Range("J18") = ws.Range("D1")
                End With
            End With
        Next ws
        Application.ScreenUpdating = True
    End Sub

  6. #6
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    8,298
    Post Thanks / Like
    Mentioned
    95 Post(s)
    Tagged
    5 Thread(s)

    Default Re: VBA to Copy and Paste From One Workbook to Another

    You are very welcome.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

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
  •