vba collate each sheets data into Master using Redim Preserve Array

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
766
Office Version
  1. 2010
Platform
  1. Windows
Hi Team

I have 5 sheets of which 4 sheets contains data and each sheets has 10 columns.

My task is to add each sheets data into array using Redim Preserve

and Paste in Master sheets range ("A1")

Thanks in advance for your help!

Option Explicit
VBA Code:
Sub Array_Two_Dimension_With()
    Dim arr() As Variant
    Dim Master_sht As Worksheet
    Dim check As Boolean
    Dim sh As Worksheet
    
    Dim file As Long
    Dim lr As Long
        
    For Each sh In ThisWorkbook.Worksheets
        If sh.Name <> "Master" Then
                file = file + 1
            If file = 1 Then
                arr = sh.Range("a1").CurrentRegion.Value
            Else
                arr = sh.Range("a1").CurrentRegion.Offset(1).Resize(lr - 1) 'Exclude Header
            End If
        End If
        
    Next sh

sheets("Master").range("a1").resize(ubound(arr),ubound(arr).value = arr   paste stored arrays values in Master sheet.

End Sub


Thanks
mg
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Watch MrExcel Video

Forum statistics

Threads
1,114,084
Messages
5,545,860
Members
410,711
Latest member
Josh324
Top