Transfer information to a different sheet in workbook and allow for continous updating and formatting

Question

I have a data entry page which feeds into a master using a VBA code & command button. I need to 1. either have the new information feed to the top of the master list without an additional row already being in place or 2. need the information to filter into separate tabs by region with the most recent listed at top. The problem I'm running into is how to make the tabs for each region update automatically from the master sheet. All of this is in one workbook.

VBA Code

Private Sub CommandButton1_Click()  

    Dim ProjectNumber As String  
    Dim Region As String  
    Dim Customer As String  
    Dim PlantName As String  
    Dim State As String  
    Dim PlantNumber As String  
    Dim Contact As String  
    Dim PhoneNumber As String  
    Dim Scope As String  
    Dim Value As String  
    Dim Year As String  
    Dim MW As String  
    Dim MFG As String  

    Worksheets("Entry").Select  
    ProjectNumber = Range("ProjectNumber")  
    Region = Range("Region")  
    Customer = Range("Customer")  
    PlantName = Range("PlantName")  
    State = Range("State")  
    PlantNumber = Range("PlantNumber")  
    Contact = Range("Contact")  
    PhoneNumber = Range("PhoneNumber")  
    Scope = Range("Scope")  
    Value = Range("Value")  
    Year = Range("Year")  
    MW = Range("MW")  
    MFG = Range("MFG")  
    Worksheets("Experience").Select  
    Worksheets("Experience").Range("A441").Select  
    If Worksheets("Experience").Range("A441").Offset(1, 0) <> "" Then  
    Worksheets("Experience").Range("A441").End(xlDown).Select  
    End If  
    ActiveCell.Offset(1, 0).Select  
    ActiveCell.Value = ProjectNumber  
    ActiveCell.Offset(0, 1).Select  
    ActiveCell.Value = Region  
    ActiveCell.Offset(0, 1).Select  
    ActiveCell.Value = Customer  
    ActiveCell.Offset(0, 1).Select  
    ActiveCell.Value = PlantName  
    ActiveCell.Offset(0, 1).Select  
    ActiveCell.Value = State  
    ActiveCell.Offset(0, 1).Select  
    ActiveCell.Value = PlantNumber  
    ActiveCell.Offset(0, 1).Select  
    ActiveCell.Value = Contact  
    ActiveCell.Offset(0, 1).Select  
    ActiveCell.Value = PhoneNumber  
    ActiveCell.Offset(0, 1).Select  
    ActiveCell.Value = Scope  
    ActiveCell.Offset(0, 1).Select  
    ActiveCell.Value = Value  
    ActiveCell.Offset(0, 1).Select  
    ActiveCell.Value = Year  
    ActiveCell.Offset(0, 1).Select  
    ActiveCell.Value = MW  
    ActiveCell.Offset(0, 1).Select  
    ActiveCell.Value = MFG  
    Worksheets("Entry").Select  
    Worksheets("Entry").Range("C6").Select  

End Sub

Always best to show your existing code. – Tim Williams Jan 26 '16 at 00:25 — Tim Williams, Jan 26 '16 at 00:25

score 0 · Answer 1 · edited May 23 '17 at 11:52

The following inserts a new blank row immediately below row 441 and stuffs the 13 string values into the new row's columns A:M.

Private Sub CommandButton1_Click()
    Dim vSTRs As Variant

    With Worksheets("Entry")
        vSTRs = Array(.Range("ProjectNumber").Value2, .Range("Region").Value2, _
                      .Range("Customer").Value2, .Range("PlantName").Value2, _
                      .Range("State").Value2, .Range("PlantNumber").Value2, _
                      .Range("Contact").Value2, .Range("PhoneNumber").Value2, _
                      .Range("Scope").Value2, .Range("Value").Value2, _
                      .Range("Year").Value2, .Range("MW").Value2, .Range("MFG").Value2)
    End With

    With Worksheets("Experience")
        .Cells(442, "A").EntireRow.Insert
        .Cells(442, "A").Resize(1, UBound(vSTRs) + 1) = vSTRs
    End With
End Sub

This should fulfill your first option.

_{See How to avoid using Select in Excel VBA macros for more methods on getting away from relying on select and activate to accomplish your goals.}

Transfer information to a different sheet in workbook and allow for continous updating and formatting

1 Answers1