CODEWITHSUNDEEP
excelvba
user1537587
user1537587

Reputation: 11

copy data into different range in another workbook

The code below copies all worksheet contents from source workbook into destination workbook. Worksheet names are exactly same. The code copies the data from source in exactly the same order/range ("A2:A700," & _ "D2:D700," & _"C2:C700") into destination workbook. However, I want the data from source in the range above to go into a different range(I3,k3 and AC3) on the destination workbook. Any assistance is appreciated.

Option Explicit

    Sub seunweb()
    'this macro copies from one workbook to another

    Dim wbSource As Workbook, wbDestination As Workbook
    Dim ws As Worksheet, rng As Range
    Dim NextRow As Long, LastRow As Long

    Application.ScreenUpdating = False

        Set wbSource = Workbooks.Open("D:\test.xls")
        Set wbDestination = ThisWorkbook
    For Each ws In wbSource.Sheets

    For Each rng In ws.Range("A2:A700," & _
                                     "D2:D700," & _
                                     "C2:C700").Areas
                wbDestination.Sheets(ws.Name).Range(rng.Address).Value = rng.Value

            Next rng

        Next ws

        wbSource.Close SaveChanges:=False

        Application.ScreenUpdating = True

    End Sub

Upvotes: 0

Views: 373

Answers (1)

chris neilsen
chris neilsen

Reputation: 53127

Instead of your for loop, use somthing like

Set rng = ws.Range("A2:A700")
wbDestination.Sheets(ws.Name).Range("I3").Resize(rng.Rows.Count, rng.Columns.Count).Value = rng.Value

Set rng = ws.Range("D2:D700")
wbDestination.Sheets(ws.Name).Range("K3").Resize(rng.Rows.Count, rng.Columns.Count).Value = rng.Value

' continue this this for each source range

Upvotes: 0

Related Questions