VBA loop copy & paste range to same worksheet name of another workbook

chloe29

New Member
Joined
Jan 19, 2016
Messages
4
Hi, I'm trying to copy the same range of cells from all worksheets in Workbook1 and paste it in the respective same-named worksheet in Workbook2.

Example:
Copy Cells A2:A5 in Sheet1 of Workbook1
Paste to Cell A2 in Sheet1 of Workbook2

Copy Cells A2:A5 in Sheet2 of Workbook1
Paste to Cell A2 in Sheet2 of Workbook2

Copy Cells A2:A5 in Sheet3 of Workbook1
Paste to Cell A2 in Sheet3 of Workbook2

Repeat for all the other sheets.

----------------

However, with my coding below, the data did not paste to the correct worksheet. Any idea what went wrong? Thanks

Code:
Sub Button1_Click()

    Dim SourceWb As Workbook, DestWb As Workbook
    Dim SourceWs As Worksheet, DestWs As Worksheet
    Dim WsName As String
    Dim CopyRng As Range


    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    
    Set SourceWb = ThisWorkbook
    'Set SourceWs = SourceWb.Worksheets
    
    Set DestWb = Workbooks.Open("C:\Users\sy\Desktop\destination.xlsx", , True) 'Readonly = True
    
    'Loop through all worksheets and copy the data to the DestWs
    For Each SourceWs In SourceWb.Worksheets
    
        'Fill in the range that you want to copy
        Set CopyRng = SourceWs.Range("A2:A5")
        
        CopyRng.Copy
        
        WsName = SourceWb.ActiveSheet.Name
        Set DestWs = DestWb.Worksheets(WsName)
        
        With CopyRng
        DestWs.Cells(Last + 1, "A").Resize(.Rows.Count, .Columns.Count).Value = .Value
        End With
        
    Next


ExitTheSub:


    Application.Goto DestWs.Cells(1)




    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With


End Sub
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
How about
Code:
    For Each SourceWs In SourceWb.Worksheets
    
        'Fill in the range that you want to copy
        Set CopyRng = SourceWs.Range("A2:A5")
        
        Set DestWs = DestWb.Worksheets(SourceWs.Name)
        
        With CopyRng
        DestWs.Cells(Last + 1, "A").Resize(.Rows.Count, .Columns.Count).Value = .Value
        End With
        
    Next
 
Upvote 0
Cross posted http://www.vbaexpress.com/forum/sho...ge-to-same-worksheet-name-of-another-workbook

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

Please supply a link to the other site(s) wher you have asked this question.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,995
Members
448,539
Latest member
alex78

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