How to Auto-populate in different sheets with a command button


Hello all. I am an architect and I am trying to develop a code in excel for organizing our projects. 

So far I have a workbook with two tabs. Sheet1 is called "Entry", Sheet2 is called "NumericalOrder", Sheet3 is called "Alphabetical Order", and Sheet 4 is called "Civil". The headers on the Entry and Numerical Order sheets is Project #, Project Name, Project Industry, Project Type, and Tracking Sheet across row 1.

I am using a command button so our office manager will enter in the needed information on the Entry sheet under the necessary headers. The command button moves all the information into the NumericalOrder and AlphabeticOrder sheet under the correct headers, sorts the lists correctly, and also clears the info in the Entry sheet. This part I have figured out.

What I'm having toubles with now is that we would like to populate lists by project industry with the click of this command button as well.  What this would mean is when entering "Civil" in the Project Industry Column(Column C) , I would like this row of information to be sent to the Civil sheet.

What do I need to add to my code to get this sorting issue out of my way? I have been watching tutorials on coding for the past two days without having any prior knowledge and I can't find any information to answer my current question!! I hope you all can help 

Here is my current working VBA Command Button code:

Private Sub CommandButton1_Click()
Dim ProjectNumber As String, ProjectName As String, ProjectIndustry As String, ProjectType As String, TrackingSheet As String
Dim nextrow As Long
With Worksheets("Entry")
    ProjectNumber = Range("A2")
    ProjectName = Range("B2")
    ProjectIndustry = Range("C2")
    ProjectType = Range("D2")
    TrackingSheet = Range("E2")
End With

With Worksheets("NumericalOrder")
    nextrow = .Range("A" & .Rows.Count).End(xlUp).Row + 1
    .Range("A" & nextrow).Resize(1, 5).Value = Array(ProjectNumber, ProjectName, ProjectIndustry, ProjectType, TrackingSheet)
    .Range("A1").CurrentRegion.Sort KEY1:=.[A2], order1:=xlDescending, Header:=xlYes
End With

With Worksheets("AlphabeticalOrder")
    nextrow = .Range("A" & .Rows.Count).End(xlUp).Row + 1
    .Range("A" & nextrow).Resize(1, 5).Value = Array(ProjectNumber, ProjectName, ProjectIndustry, ProjectType, TrackingSheet)
    .Range("A1").CurrentRegion.Sort KEY1:=.[B2], order1:=xlAscending, Header:=xlYes
End With


End Sub


Please don't forget to select the answer that worked best for you! Just click the Select Answer button at the bottom of the desired answer. If one didn't work, please comment for clarification.
don (rep: 1247) Jun 28, '17 at 9:23 am
Add to Discussion



Welcome to the forum!

You can try this...

If ProjectIndustry = "Civil" Then

    destinationSheet = "Civil"

ElseIf ProjectIndustry = "Other" Then

    destinationSheet = "Other"

End If

'put value in sheet
Sheets(destinationSheet).Range("A1").Value = "Something"

Keep expanding the IF statement for as many options as you need.



Answer the Question

You must create an account to use the forum. Create an Account or Login