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

[ebineg]

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.

 

[ebineg]

any help on this is highly appreciated

 

[Joe4]

Try this:

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

 

[ebineg]

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.

 

[Joe4]

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?

 

[ebineg]

Yeah i changed everything that are needed to be changed, and i have this macro in "source.xlsm sheet.

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

 

[Joe4]

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:

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

 

[ebineg]

That was flawless Many thanks you are awesome.

 

[Joe4]

You are welcome!

 

[ebineg]

Hi joe4,

Could you please take a look in the below link? i am actually waiting on this for very long time , i hope you can help me out
https://www.mrexcel.com/forum/excel...g-random-samples-one-sheet-another-sheet.html