VBA to auto populate a table when inputiing values from other cells and add row

[ceebeedee]

Hi and thanks for taking the time to read my issue.

The title might not be very explanatory so I have detailed what I am attempting below.

I have a table in columns B-D starting at row 2, what I am attempting to achieve is to input data in cells A2, A4 & A6 to correspond with columns B,C & D, so input in cell A2 populate column B and so forth, when finished inputting in A6 and hit enter a new row is created at the bottom of the table.

As the table, although only 3 columns wide, has lots of rows already and will continue to grow it is painful to keep scrolling to the end to input the information.

Any help would be gratefully appreciated.

Many thanks in advance for looking

Chris

 

[igold]

Hi Chris,

Are you saying that the info you input into A2, A4, A6 you want copied as the new last row to columns B,C,D respectively?

If that is the case would it work if when you activated the sheet or opened the workbook (or something similar) that the sheet automatically scrolled down to the last row of current data so that you could enter the info directly into the last row of data into columns B,C,D.

igold

 

[ceebeedee]

Hi igold

That would work, I am assuming that I would still be able to use the header row drop downs?

 

[igold]

I have a sheet that I constantly enter info into the last row. It is basically a sheet that I use to track expenses, a log sheet of sorts. At any rate there are times when I need to go back and check out info from prior rows. When I wanted to enter new info, I found it to be a tremendous pain to have scroll back down to the last row to enter new info. So I use a small code that works whenever I switch from different worksheet back to the log sheet, it automatically brings the last row up so I can immediately write to the first blank row.

I could change it to whenever you open the workbook or keep it as is- which is whenever you click on another sheet tab and then back to the original sheet in will bring up the last row.

I just need to know what the name of your worksheet is, and which column can be used to find the last row of data. From you post, it would either be B,C, or D whichever column will always have data in it indicating the last row of data.

This would not affect the header row dropdowns.

igold

 

[ceebeedee]

I have a sheet that I constantly enter info into the last row. It is basically a sheet that I use to track expenses, a log sheet of sorts. At any rate there are times when I need to go back and check out info from prior rows. When I wanted to enter new info, I found it to be a tremendous pain to have scroll back down to the last row to enter new info. So I use a small code that works whenever I switch from different worksheet back to the log sheet, it automatically brings the last row up so I can immediately write to the first blank row.

I could change it to whenever you open the workbook or keep it as is- which is whenever you click on another sheet tab and then back to the original sheet in will bring up the last row.

I just need to know what the name of your worksheet is, and which column can be used to find the last row of data. From you post, it would either be B,C, or D whichever column will always have data in it indicating the last row of data.

This would not affect the header row dropdowns.

igold

Qualifications is the name of the worksheet and all 3 columns will have data, hope this is what you want, cheers.

 

[igold]

Hi,

See if this code does what you want. This code should be placed in the "ThisWorkbook" module in the VBA editor..

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

    If Sh.Name = "Qualifications" Then
        Application.ScreenUpdating = False
        Dim lrow As Long
        lrow = Cells(Rows.Count, 2).End(xlUp).Row
        ActiveWindow.ScrollRow = lrow - 15
        ActiveSheet.Range("B" & lrow).Offset(1, 0).Select
        Application.ScreenUpdating = True
    End If

End Sub

If you need help doing that, follow these instructions…

1. Open your workbook in Excel.
2. Press Alt+F11 to open the Visual Basic Editor (VBE).
3. Double-click on "ThisWorkbook" in the "Project-VBAProject" pane (at the top left corner of the editor window).
4. Copy the VBA code (from this page) and paste it to the right pane of the VBA editor ("ThisWorkbook" window).
5. Save your workbook as "Excel macro-enabled workbook". Press Crl+S, then click the "No" button in the "The following features cannot be saved in macro-free workbook" warning dialog.
The "Save as" dialog will open. Choose "Excel macro-enabled workbook" from the "Save as type" drop-down list and click the Save button.
6. Press Alt+Q to close the Editor window and switch back to your workbook.

This code will trigger whenever you click on the "Qualifications" worksheet from any other worksheet in your workbook.

If this is not what you wanted, let me know and we can create anything you want to trigger the code (like a button or something)...

It is also a good idea to test this on a backup copy of your data.

igold