Excel

Change Column A and rest of row follows

Ease of Use

Easy

Version tested with

2000 

Submitted by:

lucas

Description:

Add a new number in row A and the values in the row above are copied down. 

Discussion:

When entering data I often find myself re-entering data that is in the row above. This script allows you to decide which of the data above is repetitive and copies it down for you. The code is commented to explain how to decide which data is copied down and how to select it by two different methods. Two different methods are shown in the code. To use the other method, just commet out the array method. 

Code:

instructions for use

			

Put this code the the sheet module: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) 'setup to extablish column A as the Target Column to trigger the routine If Target.Columns.Count > 1 Then Exit Sub If Target.Row < 2 Then Exit Sub If Target.Column <> 1 Then Exit Sub Dim x As Long x = Target.Row 'METHOD 1 'Comment out any columns you do not wish to be copied down to the next row. 'One Column At A Time 'Copies data from Column B to the next Row. 'Cells(x - 1, 2).Copy Cells(x, 2) ''Copies data from Column C to the next Row. 'Cells(x - 1, 3).Copy Cells(x, 3) ' ''2 Columns at a time ''Copies data from Column D & E to the next Row. 'Range(Cells(x - 1, 4), Cells(x - 1, 5)).Copy Cells(x, 4) 'Range(Cells(x - 1, 6), Cells(x - 1, 7)).Copy Cells(x, 6) 'Range(Cells(x - 1, 8), Cells(x - 1, 9)).Copy Cells(x, 8) 'Range(Cells(x - 1, 10), Cells(x - 1, 11)).Copy Cells(x, 10) 'Range(Cells(x - 1, 12), Cells(x - 1, 13)).Copy Cells(x, 12) 'METHOD 2 'another good option is an array Dim Cols As Variant Dim C As Variant 'Add/Delete column numbers as required Cols = Array(2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12) For Each C In Cols Cells(x - 1, C).Copy Cells(x, C) Next End Sub

How to use:

  1. Right click on the sheet tab you wish to use the code on
  2. Click on "View Code"
  3. Paste the code above into the module pane.
  4. Close the VBE
  5. You should have your headers in place and one row of data before you use it.
 

Test the code:

  1. Click on Cell A of the next empty row and enter a number(or anything)
  2. The selected data from the row above is copied down to your new row.
  3. You can select which data is copied down by changing the code.
  4. Code is commented to explain how to do this.
 

Sample File:

change Col A and rest of row follows.zip 8.72KB 

Approved by mdmackillop


This entry has been viewed 154 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express