Adding data from Array to end of existing data in another workbook

[daopsmatt]

Hello all,

I have banging my head against the wall for a couple of days on this one. I know it must be so simple I am overlooking something.

Goal: Copy data from one workbook into an Array. Then paste that data into another workbook after the last row of data already there.
Here is what I already have. My challenge, I think is, figuring out the "next available row in spreadsheet 2 and cycling through the Array to paste the data.

Challenge, both workbooks, will have unknown number of rows to start with because they are system generate reports with data that changes.

Any help would be great, like I said it probably is so simple I am just overthinking it.

Thank you.

Sub BarioArray()

' Copy column and paste at end of another columns data
'
    Dim arrBario() As Variant
    Dim myCount As Integer
    Dim lastRow As Long
    Dim wbBario As Workbook: Set wbBario = Application.Workbooks("Bario.xls")
    Dim wsBario As Worksheet
    Set wsBario = wbBario.Sheets("report")
    
' Fill Array
    arrBario = Range("A1").CurrentRegion
    
' empty array
With Worksheets("Sheet1")
    For myCount = LBound(arrBario) To UBound(arrBario)
        .Cells((0 + myCount), 1).Value = arrBario(myCount, 1)
    Next myCount
End With

Range("A1").Select
lastRow = wsBario.Cells(Rows.Count, 1).End(xlUp).Row
nextRow = Range(("A") & (lastRow + 1)).Select

With wsBario
    For myCount = LBound(arrBario) To UBound(arrBario)
      .Cells((nextRow + myCount), 1).Value = arrBario(myCount, 4)
    Next myCount
End With

End Sub

 

[DanteAmor]

I didn't quite understand which column you want to copy, or where you want to paste it.
But if you only want to pass column A of sheet1 to report sheet, then:

Sub copydata()
  Dim a As Variant
  a = Sheets("Sheet1").Range("A1", Sheets("Sheet1").Range("A" & Rows.Count).End(3)).Value2
  Workbooks("Bario.xlsx").Sheets("report").Range("A" & Rows.Count).End(3)(2).Resize(UBound(a)).Value = a
End Sub

 

[daopsmatt]

Thank you DanteAmor

Sorry, I should have added that information, you are correct.

I am creating an array with the information on the Bario sheet. Currently, I am trying to the information in the array spot #4 and paste it at the end of the data in Column A same sheet, just so I can figure that out.

Ultimately, I want to take the data on the array from Bario and move it to Air List Workbook, I am copying 6 columns of data, and want to up it at the end of the existing data in the Air List workbook same columns.

 

[DanteAmor]

Sorry, I still don't understand.
You want to copy data from a source sheet to another target sheet, if so, you could explain like this:

Sheet("NameA").range("X") origin.
Sheet("NameB").range("Y") destination.

Replace "nameA", "NameB", "X", "Y" with the real names.

 

[daopsmatt]

Sorry if I am not explaining it correctly, I am very novice at VBA and self-taught so I know that doesn't help.

I was doing a Copy Paste from sheet to the other but was trying to speed up the process by using an array and using less code that if someone had to clean it up later they could.

Thank you so much for your help. I really appreciate it.

 

[DanteAmor]

Okay, using a array will help speed up the process.
But if you explain what range you want to copy from which sheet and where you want to paste it and in which cell, I help you simplify the code.

You should not even explain it with VBA code, only explain with simple words source sheet, source cells, target sheet, target cell.