CODEWITHSUNDEEP
excelvbaworksheet
user50506
user50506

Reputation: 191

Excel VBA code to copy several ranges to specific sheet in another workbook

I found some code here that copies my data to another sheet which works fine. I am trying to extend it so that it copies to another Workbook and Specific Worksheet.

Sub Put_Data()
Dim lastrowDB As Long, lastrow As Long
Dim arr1, arr2, i As Integer

With Sheets("GasMe18-19")
    lastrowDB = .Cells(.Rows.Count, "A").End(xlUp).row + 1
End With

arr1 = Array("A", "G", "H", "L")
arr2 = Array("Q", "R", "S", "T")

For i = LBound(arr1) To UBound(arr1)
    With Sheets("GasMe18-19")
         lastrow = Application.Max(4, .Cells(.Rows.Count,   arr1(i)).End(xlUp).row)
         .Range(.Cells(4, arr1(i)), .Cells(lastrow, arr1(i))).Copy
         Sheets("Sheet1").Range(arr2(i) & 2).PasteSpecial xlPasteValues
    End With
Next
Application.CutCopyMode = False
End Sub

How can this be modified to put the data ranges into another Workbook? I put Sheet1 in the home Workbook and everything seems fine and I like the existing code base. The originating Workbook.Worksheet has 4 columns of data that I add to every few days. I then copy and paste into another Workbook2.WorkSheet2 for plotting. I prefer to copy everything from Row 4 to the last row of data for the 4 columns.

Upvotes: 0

Views: 58

Answers (1)

K.Dᴀᴠɪs
K.Dᴀᴠɪs

Reputation: 10139

You need to qualify your range with the workbook in question

Dim wbSource As Workbook, wbTarget As Workbook
Set wbSource = ThisWorkbook
Set wbTarget = Workbooks("TheTargetWBName")

...

For i = LBound(arr1) To UBound(arr1)
    With wbSource.Worksheets("GasMe18-19")
         lastrow = Application.Max(4, .Cells(.Rows.Count, arr1(i)).End(xlUp).Row)
         .Range(.Cells(4, arr1(i)), .Cells(lastrow, arr1(i))).Copy
         wbTarget.Worksheets("Sheet1").Range(arr2(i) & 2).PasteSpecial xlPasteValues
    End With
Next

Take note how the sheets("Sheet1").Range(arr2(i) & 2).PasteSpecial xlPasteValues line has now been fully qualified with the target workbook, using the previously declared variable wsTarget. This variable was first set to the name of the workbook using the line:

Set wbTarget = Workbooks("TheTargetWBName")

If the target workbook is not open, then you would simply change to

Set wbTarget = Workbooks.Open("TheTargetWBName")

Upvotes: 3

Related Questions