daopsmatt
New Member
- Joined
- Jan 31, 2017
- Messages
- 4
- Office Version
- 365
- 2019
- 2016
- Platform
- Windows
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.
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.
VBA Code:
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