PDA

View Full Version : Auto-populate a master list from info in other sheets without adding spaces



lulul
03-09-2016, 04:42 PM
Hi everyone,

I am (very) unfamiliar with VBA, so please be patient :)

I have three tabs (one per region) with information on Client Name, Contract Amount, and Manager (see workbook attached). I would like to automatically populate a master list (in MASTER tab) pulling information from my other three tabs. You will notice that the list in each Region tab goes up to 15. This is because that's the maximum number of entries we would have in a month per region. I'd like the master list to automatically populate as we enter information in the other tabs, but ignore any empty spaces. For example, if we only have five entries in the North America tab, I'd like the master list to show the first entry in the Europe tab right after the last North America entry.

Also, because we have the regions split in different tabs, we do not manually track region per row entry. In the master list, I'd like to add each entry to each specific region. Please see the workbook attached with a sample of what I'd like the master list to look like.

Many thanks in advance for your help!
Lulul

Paul_Hossler
03-09-2016, 06:01 PM
Something like this maybe. There's many ways to be more clever, but this is probably the most straight forward, and most easily expandable

In MASTER there is a worksheet event that runs every time you activate the worksheet. The event clears existing data and rebuilds from the 3 sheets



Option Explicit
Private Sub Worksheet_Activate()
Dim r As Range

Application.ScreenUpdating = False

Set r = Me.Cells(1, 1).CurrentRegion
Set r = r.Cells(2, 1).Resize(r.Rows.Count, r.Columns.Count)
r.Clear
Call AddData("North America")
Call AddData("Europe")
Call AddData("Asia")
Application.ScreenUpdating = True

End Sub




The subroutine AddData is called 3 times to add the data from the worksheet passed to it to the bottom of MASTER's data



Option Explicit

Sub AddData(WS As String)
Dim i As Long, o As Long

With Worksheets("MASTER")
o = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
End With

With Worksheets(WS)
For i = 2 To .Cells(1, 1).CurrentRegion.Rows.Count
If Len(.Cells(i, 2).Value) = 0 Then Exit Sub

Worksheets("MASTER").Cells(o, 1).Value = .Cells(i, 2).Value
Worksheets("MASTER").Cells(o, 2).Value = .Cells(i, 3).Value
Worksheets("MASTER").Cells(o, 3).Value = .Cells(i, 4).Value
Worksheets("MASTER").Cells(o, 4).Value = WS

o = o + 1

Next I

End With
End Sub




Other features can be added easily so ask if you get stuck