tables from 4 sheets into a VBA array macro

[ash.mak]

Hi ALL,

I have data tables on 4 sheets. Each have 27 columns ( with same column headings ) but rows range from 500 to 29000.
I want to create a macro to fetch data from all 4 sheets into a single array and paste it as a single table on fifth sheet as values. Is it possible?

 

[smiths87]

Basic answer is yes. Several ways to go about it though, and you can iterate through the worksheets you want to include, etc. etc. But say I had data in Sheet1, and Sheet2, and I wanted to copy over the sheet1 data to Sheet3, then do the same with the data on Sheet2. Below code will do that (very simplistically and with no error checking.) But you'll probably get the basic idea.

Sub grabandappend()
Dim firstRng As Range
Dim secondRng As Range
Dim endrow As Long

Set firstRng = Sheets("Sheet1").UsedRange
firstRng.Copy Sheets("Sheet3").Range("A1")

endrow = Range("A65536").End(xlUp).Row

Set secondRng = Sheets("Sheet2").UsedRange

secondRng.Offset(1, 0).Copy Sheets("Sheet3").Range("A" & endrow + 1)

End Sub

 

[ash.mak]

Thanks you smiths87. (sorry for the late reply in thanking you.)

I wanted to do this using the VBA arrays to,learn how to append sets of data to an array and any other ways to play with the arrays. I have learnt that arrays reside in memory and are extremely fast. This helps me perform any calculations on each record in an array as I do payrolls and other financial stuff.

Your method works great. It is the simple yet best solution. I wish I could develop understanding of VBA Arrays as I deal with 37 different locations globaly and each payroll is a different sheet.