Repeat contents of cell above

**mys Scott** · 08-30-2011, 07:35 AM

I am wondering if it is possible to fill in the contents of a cell, based on the cell directly above it, once the target cell is "tabbed" in to. I've tried using the ctrl+shift+" key combination to fill in the cell contents, but it's barely a time saver. Also, it doesn't work with some of the VBA code I have set up for the date format and time entries.
I'm trying (without much success) to write the code to enable columns A, B and C of the attached worksheet to auto-fill based on the record above but only once the cell is tabbed in to. What's important though is that the entry stays highlighted (for over-typing) so that if the record is not supposed to be a repeat of the one above it then the new data is easily over-typed (and without additional keystrokes or "deleting") . Every 4 to 7 records or so go to a new date/person/etc. so no repeat would be desired, hence the need to easily overtype these auto-filled contents..
If this is not doable on a per-column basis then it might be an acceptable alternative to have the entire record repeated once the first cell is tabbed in to. I don't know if the functionality of a cell being tabbed in to can be recognized as being different than if the cell is clicked on or arrowed in to. Hopefully so, as it seems this functionality sort of hinges on that criteria. Thanks in advance for any guidance or help.

**teylyn** · 08-30-2011, 07:51 AM

Hello,

a couple of things:

- the narrative above is a bit hard to understand. You have to take into account that we don't know your spreadsheet and your desired outcome as well as you do, so it may pay off to spend some words to explain the bigger picture.

- you are using Excel 2010, according to your profile. The attached file contains a table with many rows pre-filled with formulas. You can avoid that setup by using an Excel Table (Insert > Table). An Excel table will carry over any formatting and formulas to a new row that is added to the table (have a read in the Excel help files)

- if you want to copy the value from the cell above, select the cell and hit Ctrl-D. In case of the date in column A, you can click the cell above the new row and drag the fill handle to increase the value by one day (unless you use Ctrl-D to copy the same date from the row above).

Looking at your data, I don't quite see a logic or rule set that determines if or when a cell should be copied or incremented.

So, with a little bit of Excel savvy (using Tables, using Ctrl-D to copy down, using the fill handle to increment a date) you may be on your way without a single row of VBA.

I find that very often people ask for a macro because they don't know how to drive the user interface. Maybe there's a learning curve to be conquered.

cheers,

**snb** · 08-30-2011, 08:20 AM

You could try (after deleting the worksheet_change event) :

 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Target.Row < 3 Then Exit Sub
  Select Case Target.Column
  Case 2, 3
     Target = Split(Target.Offset(-1))(0)
  End Select
End Sub

**mys Scott** · 08-31-2011, 06:23 AM

Tetylen
Thanks for your response. I've read and reread the post, and the the only simpler way I can ask the question is "How can I globally (worksheet) and automatically fill in an entire record as a copy of the record above it simply by tabbing into the new/lower/target record?"
When working with the worksheet in protected mode a new record is tabbed into whenever the last unprotected cell in the record above it is tabbed out of. So the rule set would be every new record, when tabbed into (as opposed to clicked into) would be autofilled with the exact contents of the previous record., every time.
The attached file is populated with sample data (including the formulas I need for the functionality I already have) so anyone inclined to offer code suggestions can see the interaction I need. For example, the date formatting and and time formatting VBA I have already does not play well with ctrl-D (I tried that before I posted) or any other copy method other than ctrl+c and ctrl+v. The clipboard copy/paste approach does work, but is an additional mouse click/keystroke procedure that, when repeated hundreds of times, adds significantly to data entry time.
An alternative approach can be to execute a simple key combination to enact a script which duplicates a record into the current record, no matter where the cursor is. A little dangerous, but manageable. So I imagine something like alt+shift+P selecting the entire record (row) above (relative to the current cursor location), copying it to the clipboard and pasting it into the current record. Is this manageable? This eliminates the need for an event-based script to manage the heavy lifting, and as I've discovered by doing the operation manually seems to handle the date/time formatting VBA I have very well.
Any pointers on this approach would be appreciated.

To SNB:
Why do I need to delete the worksheet_change event? I need this to execute another script I have running. Please elaborate if possible. Thanks for the input.

So,

**Kelshaer** · 08-31-2011, 03:40 PM

Well, The simplest way would be to make a filter to display only the rows with the BLANK values. then go to the first row and type a formula that is equal to the above hidden cell. for example if the first blank cell is "A5" then type in it "=A4" then drag it down to all other blank cells.

**snb** · 08-31-2011, 04:18 PM

The code changes the content of a cell. That triggers the worksheet_change event. You can prevent that deleting the Worksheet_change code, or using Application.enableevents=false.