Inserting a record into multiple tables based on the Master Table.

[fbagirov]

I have a workbook with multiple tables in different tabs. One of the tabs has
the "Master Table", that has the names of the sales people in the first
column and then their data in the rest of the columns. All tables in the
other tabs have sales people in the first columns, and then different types
of data, depending on the tab.

I need to write a macro, so that when a new sales person raw inserted into
teh "Master Table", the tables in the other tabs would also update themselves
with the new sales person's name.

The challenge is that I need the raws to be sorted alphabetically. I managed
to write a macro to insert and sort it in teh "Master Table", but then don't
know how to proceed with the other tables ? Here is what I 've got so far:

Sub GetData()
Dim NextRow As Long
Dim NameEntry As String, SiteIDEntry As String, StartDateEntry As
String, _
EndDateEntry As String, RenewalEntry As String
Do
'determine next empty row
NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1

MsgBox ("Next Empty Row is row " & NextRow)

'Prompt the data
NameEntry = StrConv(InputBox("Enter the VAR Name or Cancel to
Exit"), vbUpperCase)
If NameEntry = "" Then Exit Sub
SiteIDEntry = StrConv(InputBox("Enter the VAR Site ID"), vbUpperCase)
If SiteIDEntry = "" Then Exit Sub
StartDateEntry = InputBox("Enter the Start Date as MM/DD/YYYY")
If StartDateEntry = "" Then Exit Sub
EndDateEntry = InputBox("Enter the End Date as MM/DD/YYYY")
If EndDateEntry = "" Then Exit Sub
RenewalEntry = InputBox("Is this a contract renewal?")
If RenewalEntry = "" Then Exit Sub


'write the data
Cells(NextRow, 1) = NameEntry
MsgBox ("Wrote NameEntry")
Cells(NextRow, 2) = SiteIDEntry
MsgBox ("Wrote SiteIDEntry")
Cells(NextRow, 3) = StartDateEntry
Selection.NumberFormat = "mm/dd/yyyy"
MsgBox ("Wrote and Formatted the StartDateEntry")
Cells(NextRow, 4) = EndDateEntry
Selection.NumberFormat = "mm/dd/yyyy"
MsgBox ("Wrote and Formatted the EndDateEntry")
Cells(NextRow, 5) = RenewalEntry
MsgBox ("Wrote the RenewalEntry")

'sort alphabetically
Range("A1").Select
Range("A1").CurrentRegion.Sort Key1:=Range("A2"),
Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Loop


' here would go the procedure to update table in tab1
'here would go the procedure to update table in tab2

End sub


Thanks for your help!

 

[Dave Peterson]

Maybe...

Option Explicit
Sub GetData()
Dim NextRow As Long
Dim NameEntry As String
Dim SiteIDEntry As String
Dim StartDateEntry As String
Dim EndDateEntry As String
Dim RenewalEntry As String
Dim MstrTabWks As Worksheet
Dim OtherWksNames As Variant
Dim TestWks As Worksheet
Dim wCtr As Long
Dim ErrorFound As Boolean
Dim AnyExisting As Long

OtherWksNames = Array("Tab 1", "Tab 2", "Tab 3")

ErrorFound = False
For wCtr = LBound(OtherWksNames) To UBound(OtherWksNames)
Set TestWks = Nothing
On Error Resume Next
Set TestWks = Worksheets(OtherWksNames(wCtr))
On Error GoTo 0
If TestWks Is Nothing Then
MsgBox "Design Error:" & vbLf _
& OtherWksNames(wCtr) & vbLf _
& "Doesn't exist"
ErrorFound = True
End If
Next wCtr
If ErrorFound = True Then
Exit Sub
End If

Set MstrTabWks = Worksheets("Master Table")

With MstrTabWks
Do
'determine next empty row
NextRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1

MsgBox "Next Empty Row is row " & NextRow

'Prompt the data
NameEntry = StrConv( _
InputBox("Enter the VAR Name or Cancel to Exit"), vbUpperCase)
If NameEntry = "" Then Exit Sub
SiteIDEntry = StrConv(InputBox("Enter the VAR Site ID"), _
vbUpperCase)
If SiteIDEntry = "" Then Exit Sub
StartDateEntry = InputBox("Enter the Start Date as MM/DD/YYYY")
If StartDateEntry = "" Then Exit Sub
EndDateEntry = InputBox("Enter the End Date as MM/DD/YYYY")
If EndDateEntry = "" Then Exit Sub
RenewalEntry = InputBox("Is this a contract renewal?")
If RenewalEntry = "" Then Exit Sub

'write the data
.Cells(NextRow, 1).Value = NameEntry
MsgBox "Wrote NameEntry"
.Cells(NextRow, 2).Value = SiteIDEntry
MsgBox "Wrote SiteIDEntry"
With .Cells(NextRow, 3)
.Value = StartDateEntry
.NumberFormat = "mm/dd/yyyy"
MsgBox "Wrote and Formatted the StartDateEntry"
End With
With .Cells(NextRow, 4)
.Value = EndDateEntry
.NumberFormat = "mm/dd/yyyy"
MsgBox "Wrote and Formatted the EndDateEntry"
End With
.Cells(NextRow, 5).Value = RenewalEntry
MsgBox "Wrote the RenewalEntry"

'sort alphabetically
With .Range("A1").CurrentRegion
.Cells.Sort Key1:=.Columns(2), Order1:=xlAscending, _
Header:=xlNo, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
End With

For wCtr = LBound(OtherWksNames) To UBound(OtherWksNames)
Set TestWks = Worksheets(OtherWksNames(wCtr))
With TestWks
AnyExisting = Application.CountIf(.Range("a:a"), NameEntry)
If AnyExisting = 0 Then
NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
.Cells(NextRow, "A").Value = NameEntry
'any more fields to add???
End If
End With
Next wCtr
Loop
End With

End Sub