Dear All,
Am trying to write VBA code to make a 2 dimensional array from different ranges but unable to properly insert second and next ranges.
My ranges are on Col A to Col H on each sheet with possibility of different total number of rows on different sheets.
Data on each sheet looks like below
Could you please guide me as to where should I correct my code?
I know I need to insert second range after the first range but am unable to make a code for it.
Best Regards
Ashish
Am trying to write VBA code to make a 2 dimensional array from different ranges but unable to properly insert second and next ranges.
My ranges are on Col A to Col H on each sheet with possibility of different total number of rows on different sheets.
VBA Code:
Sub ticker()
'
' ticker Macro
' This macro will consider all Sheets
'
Dim thisWB As Workbook
Dim thisWS As Worksheet
Dim lastrow As Long
Dim tickerArray As Variant
Set thisWB = ActiveWorkbook
counter = 1
For Each thisWS In thisWB.Worksheets
lastrow = thisWS.Cells(thisWS.Rows.Count, 1).End(xlUp).Row
'Add data to array
If counter = 1 Then
ReDim tickerArray(1 To lastrow - 1, 1 To 8)
tickerArray = thisWS.Range("A2:H" & lastrow)
Else
tickerArray = Application.Transpose(tickerArray)
ReDim Preserve tickerArray(1 To 8, 1 To UBound(tickerArray, 2) + lastrow - 1)
tickerArray = Application.Transpose(tickerArray)
'Am not able to append the range to array below. the current code in below line replaces the array values
tickerArray = thisWS.Range("A2:H" & lastrow)
End If
counter = counter + 1
Next thisWS
End Sub
Data on each sheet looks like below
<ticker> | <date> | <open> | <high> | <low> | <close> | <vol> | Calculate |
AAB | 20200102 | 23.43 | 23.57 | 23.43 | 23.57 | 28522 | |
AAB | 20200103 | 23.52 | 23.61 | 23.43 | 23.44 | 1399 | |
AAB | 20200106 | 23.46 | 23.48 | 23.37 | 23.39 | 2953 | |
AAB | 20200107 | 23.31 | 23.47 | 23.28 | 23.47 | 64755 |
Could you please guide me as to where should I correct my code?
I know I need to insert second range after the first range but am unable to make a code for it.
Best Regards
Ashish