Excel/VBA

Advertisement


Question
QUESTION: Hi Tom

In range A1:E197 (worksheet "SPtemplate2) I have the following information: column   A first name, Column B second name, Column C year group, Column D class code and Column E target grade.

From time to time I need to add information to this.  So on a separate worksheet ("add new names") I have a range A1:E2.  Row 1 will contain the headings: A1 (first name), B1 (second name), C1 (year group) D1 (class code) and E1 (target grade).

I will enter the names, year group, class code and target grade into: A2, B2, C2, D2 and E2.

I would like a piece of code that will transfer this information into the last row of information on worksheet "SPtemplate2".  The final command should be to calculate the workbook and select cell "J1" on worksheet "enter comments"

Could you help me with this please?

Thanks in advance.

Chris Mitchell

ANSWER: Chris Mitchell,

right click on the Sheet tab for Sheet "add new names" and select view code.

Paste in this code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range, r1 As Range
Set r = Me.Range("A2:E2")
If Not Intersect(Target, r) Is Nothing Then
 If Application.CountA(r) = 5 Then
   With Worksheets("SPTemplate2")
       Set r1 = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0)
       r.Copy r1
       r.ClearContents
   End With
   With Worksheets("Enter Comments")
     .Activate
     .Range("J1").Select
   End With
 End If
End If
End Sub


Go back to excel.

Activate sheet "add new names" and enter data in the five cells A2:E2.  You can enter it in any order.  As soon as all five cells have a value, they will be copied to the next available row in sheet SPTemplate2 and the values cleared from sheet "add new names"  Then sheet "enter comments" will be activated and cell J1 selected.   Worked for me.

--
Regards,
Tom Ogilvy


---------- FOLLOW-UP ----------

QUESTION: Hi Tom

This code works perfectly. I have added another feature.  I have placed a button on the worksheet "add names".  Only after I press this button will the names be transferred to SPTemplate.  This will reduce the chance of errors when inputting names.

Could you write the code that I need to assign this button to?

Thanks in advance.

Chris

Answer
Chris Mitchell,


Assuming you have put an activeX commandbutton on sheet "add names" then right click on the sheet tab and select view code.  Put in code like this.  


Private Sub CommandButton1_Click()
Dim r As Range, r1 As Range
Set r = Me.Range("A2:E2")

   With Worksheets("SPTemplate2")
       Set r1 = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0)
       r.Copy r1
       r.ClearContents
   End With
   With Worksheets("Enter Comments")
     .Activate
     .Range("J1").Select
   End With


End Sub


worked for me.

--
Regards,
Tom Ogilvy  
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.