Qfyilyi

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.

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")