Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

I NEED HELP: How do I transfer information to another worksheet via command button and also sort that data alphabetically

0

Hello all.  I am an architect and I am trying to develope 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 transfered to NumericalOrder, the data populates in the last by numerical order in column A "Project #".  I dont want to have our office manager click sort everytime she had information put in the NumericalOrder worksheet.

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 :) I have attached my file as well.

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

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
Answer
Discuss

Discussion

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.
don (rep: 1989) Jun 22, '17 at 4:48 am
Add to Discussion

Answers

0

 Your could put another Button in the worksheet that would SORT the data.

Sub Sort2()
' SortData Macro
'
Range("A1").CurrentRegion.Sort _
      key1:=Range("A1"), order1:=xlAscending, _
      Header:=xlYes
     
      ' Move Key2 KEY3 below Key1  IF you need more sorting
      ' KEY2:=Range("A1"), order2:=xlAscending, _
      'KEY3:=Range("B1"), Order3:=xlDescending, _

End Sub

Discuss

Discussion

Right, but I don't want another button.  I'd rather have both functions work under the same command button.
crniess Jun 20, '17 at 12:09 pm
add the Sub, and you can then call it from your original sub (before your "END SUB") .  It is sometimes best to (quote from the Offspring band...) "Keep 'm Separated"!
queue (rep: 467) Jun 20, '17 at 12:29 pm
Add to Discussion


Answer the Question

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