select and Copy multiple range and paste in specific workbook in same range as copied.

ebineg

New Member
Joined
Feb 24, 2016
Messages
39
Hi,

i am trying to copy multiple Range("d1, f3, f4, f6:f9, d13:j32, d35:j54, d57:j76, d79:j98") and want to paste the copied data in the same range in the specific worksheet.

my current worksheet - file:///C:\Users\ebineg\Documents\source.xlsx (sheet1)
my destined worksheet - file:///C:\Users\ebineg\Documents\master.xlsx (sheet1)

Could anyone help me on this code?

Thanks in advance.
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Try this:
Code:
Sub MyCopyMacro()

    Dim wbMaster As Workbook
    Dim wbSource As Workbook
    Dim myRange As Range
    Dim cell As Range
    Dim myAdd As String
    
    Application.ScreenUpdating = False

    Workbooks.Open Filename:="C:\Users\Joe\Documents\master.xlsx"
    Set wbMaster = ActiveWorkbook
    
    Workbooks.Open Filename:="C:\Users\Joe\Documents\source.xlsx"
    Set wbSource = ActiveWorkbook
    Set myRange = wbSource.Sheets("Sheet1").Range("D1, F3:F4, F6:F9, D13:J32, D35:J54, D57:J76, D79:J98")
    
    For Each cell In myRange
        myAdd = cell.Address(0, 0)
        wbMaster.Sheets("Sheet1").Range(myAdd) = cell
    Next cell
    
    Application.ScreenUpdating = True
    
End Sub
 
Upvote 0
Hi joe4,

When i run the above code it opens the master.xlsm file and it stops. it is not pasting the copied data from source.xlsm book.
your thoughts?

Thanks.
 
Upvote 0
Did you change the file name & paths in the code to match yours exactly?

Also, are you putting this VBA code in one of these two workbooks, or somewhere else?
 
Last edited:
Upvote 0
Yeah i changed everything that are needed to be changed, and i have this macro in "source.xlsm sheet.

Code:
Sub MyCopyMacro()

    Dim wbMaster As Workbook
    Dim wbSource As Workbook
    Dim myRange As Range
    Dim cell As Range
    Dim myAdd As String
    
    Application.ScreenUpdating = False


    Workbooks.Open Filename:="C:\Users\ebineg\Documents\master.xlsm"
    Set wbMaster = ActiveWorkbook
    
    Workbooks.Open Filename:="C:\Users\ebineg\Documents\source.xlsm"
    Set wbSource = ActiveWorkbook
    Set myRange = wbSource.Sheets("Catalog Integration Formatter").Range("D1, F3:F4, F6:F9, D13:J32, D35:J54, D57:J76, D79:J98")
    
    For Each cell In myRange
        myAdd = cell.Address(0, 0)
        wbMaster.Sheets("Catalog Integration Formatter").Range(myAdd) = cell
    Next cell
    
    Application.ScreenUpdating = True
    
End Sub
 
Upvote 0
OK, then since the "Source" file is already open (because this is where the VBA code is), we don't need to open that in our VBA code.
Try this version:
Code:
Sub MyCopyMacro()

    Dim wbMaster As Workbook
    Dim wbSource As Workbook
    Dim myRange As Range
    Dim cell As Range
    Dim myAdd As String
    
    Application.ScreenUpdating = False

    Set wbSource = ActiveWorkbook
    Set myRange = wbSource.Sheets("Catalog Integration Formatter").Range("D1, F3:F4, F6:F9, D13:J32, D35:J54, D57:J76, D79:J98")

    Workbooks.Open Filename:="C:\Users\ebineg\Documents\master.xlsm"
    Set wbMaster = ActiveWorkbook
    
    For Each cell In myRange
        myAdd = cell.Address(0, 0)
        wbMaster.Sheets("Catalog Integration Formatter").Range(myAdd) = cell
    Next cell
    
    Application.ScreenUpdating = True
    
End Sub
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,993
Messages
6,175,842
Members
452,675
Latest member
duongtruc1610

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