How do I transfer information to another worksheet via command button and also sort that data numerically

[crniess]

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" and Sheet2 is called "NumericalOrder". 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 sheet under the correct headers and also clears the info in the Entry sheet. This part I have figured out.
Now what I want done, is when the data is transferred to NumericalOrder, the data populates in the list by numerical order in column A "Project #". I don't want to have our office manager click sort every time she had information put in the NumericalOrder worksheet.

Basically add this function to my command button so two functions happen with one click.


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:


<code>Private Sub CommandButton1_Click()
Dim ProjectNumber As String, ProjectName As String, ProjectIndustry As String, ProjectType As String, TrackingSheet As String

Worksheets("Entry").Select
ProjectNumber = Range("A2")
ProjectName = Range("B2")
ProjectIndustry = Range("C2")
ProjectType = Range("D2")
TrackingSheet = Range("E2")

Worksheets("NumericalOrder").Select
Worksheets("NumericalOrder").Range("A1").Select
If Worksheets("NumericalOrder").Range("A1").Offset(1, 0) <> "" Then
Worksheets("NumericalOrder").Range("A1").End(xlDown).Select
End If
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = ProjectNumber
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = ProjectName
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = ProjectIndustry
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = ProjectType
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = TrackingSheet

Worksheets("Entry").Select
Worksheets("Entry").Range("A2:E2").ClearContents


End Sub</code>

 

[JoeMo]

Welcome to the board!

You really don't need to do all the selecting, it just slows execution down. This should work for you assuming the sheet 'NumericalOrder' already has the row 1 headers in place when you run it.

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:=xlAscending, Header:=xlYes
End With
Worksheets("Entry").Range("A2:E2").ClearContents
End Sub

 

[crniess]

Welcome to the board!

You really don't need to do all the selecting, it just slows execution down. This should work for you assuming the sheet 'NumericalOrder' already has the row 1 headers in place when you run it.

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:=xlAscending, Header:=xlYes
End With
Worksheets("Entry").Range("A2:E2").ClearContents
End Sub

Thanks so much for the info! It worked like a charm and you've saved me hours of failure! Let's say I was doing it alphabetically with Project Name, what part of the code would I change?

 

[crniess]

Thanks so much for the info! It worked like a charm and you've saved me hours of failure! Let's say I was doing it alphabetically with Project Name, what part of the code would I change?

 

[JoeMo]

Thanks so much for the info! It worked like a charm and you've saved me hours of failure! Let's say I was doing it alphabetically with Project Name, what part of the code would I change?

You are welcome.

You would change key1 like this:
key1:= .[B2]

 

[crniess]

You're amazing JoeMo! Thanks so much. Now could I pick your brain a little more???

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.

 

[JoeMo]

You're amazing JoeMo! Thanks so much. Now could I pick your brain a little more???

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.

You are welcome.

You want to send the row 2 data from worksheet 'Entry' to the sheet 'NumericalOrder' (as the code does now), AND you want to send the the same data to a sheet whose tab name is the value appearing in C2 on the 'Entry' sheet. Is that correct? If yes, try this (I'll assume the sheets exist with headers in row 1 to accept the output.

Private Sub CommandButton1_Click()
Dim ProjectNumber As String, ProjectName As String, ProjectIndustry As String, ProjectType As String, TrackingSheet As String
Dim NextRow As Long, TabNam As String
With Worksheets("Entry")
    ProjectNumber = .Range("A2")
    ProjectName = .Range("B2")
    ProjectIndustry = .Range("C2")  'possible name of a tab in existing workbook
    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:=xlAscending, Header:=xlYes
End With
On Error Resume Next
With Sheets(ProjectIndustry)
    NextRow = .Range("A" & .Rows.Count).End(xlUp).Row + 1
    .Range("A" & NextRow).Resize(1, 5).Value = Array(ProjectNumber, ProjectName, ProjectIndustry, ProjectType, TrackingSheet)
End With
On Error GoTo 0
Worksheets("Entry").Range("A2:E2").ClearContents
End Sub

 

[crniess]

Yes, I want that command button to do all this work at one time. I'm not sure if it's possible, but you seem to be the person to know!! What I'm trying to do is have a keyword that when entered in the cell C2, and when the command button is hit, that word in the "Project Industry" column (C) corresponds with worksheet to have the information populated.

So when we're entering in a Civil project information, filling the whole row under the five headers, the row of data will populate in the "Civic" worksheet. If it was a Commercial project, the "Commercial" worksheet would populate the row of data because the word "commercial" was put into C2.

Does that make more sense?

I just want to take a moment to thank you again. You are such a great help and I truly appreciate all this work you're doing!

 

[JoeMo]

Yes, I want that command button to do all this work at one time. I'm not sure if it's possible, but you seem to be the person to know!! What I'm trying to do is have a keyword that when entered in the cell C2, and when the command button is hit, that word in the "Project Industry" column (C) corresponds with worksheet to have the information populated.

So when we're entering in a Civil project information, filling the whole row under the five headers, the row of data will populate in the "Civic" worksheet. If it was a Commercial project, the "Commercial" worksheet would populate the row of data because the word "commercial" was put into C2.

Does that make more sense?

I just want to take a moment to thank you again. You are such a great help and I truly appreciate all this work you're doing!

Did you try the code I posted in post #7? Assumes what you enter in C2 is the name of an existing sheet. So, if you enter "Civil" in C2 then there should be a sheet named "Civil" where the row2 data will be entered when the command button is clicked.

 

[NoSparks]

For the list, you could apply data validation to C2.