Excel VBA to copy

Question

My task is to create a searchable database within Excel with an entry form. I need a macro to take the data from the entry form move to the database sheet offset the active cell down 1 row and copy the values only(not the formatting)

Every time I try to run the macro I get a run-time error in the code. I have no experience with VB or VBA; please tell me what is wrong with this.

Sheets("Database").Select       'Navigates to Database worksheet
If ActiveSheet.FilterMode Then
    ActiveSheet.ShowAllData
End If                          'Clears filters
Sheets("Entry Form").Select     'Navigates back to Entry Form worksheet

Range("E10:L10").Select           ' select date, period, and data
Selection.Copy
Sheets("datatable").Select      ' navigate to datatable tab
Range("A1").Select
Selection.End(xlDown).Select    ' ctrl-down to last occupied row,
ActiveCell.Offset(1, 0).Select  ' then one more to first blank row

Selection.PasteSpecial Paste:=xlPasteValues
'Pastes data as values only into the Database worksheet

Sheets("Entry Form").Select     'Navigates to Entry Form worksheet
Application.CutCopyMode = False 'clears copy data from clipboard
Range("E10, L10").Select
Selection.ClearContents         'Clears data from drop down selections

Range("E10").Select             'Returns selection back to Date entry box

It goes the very bottom of the next page and gives a 1004 error.

How many occupied rows are on the datatable sheet to start with? If there's only one occupied row then Ctrl-Down (or its macro equivalent) from cell A1 will take you to the very last row in the worksheet. Once you have two or more rows it will work as expected — barrowc, Jul 23 '15 at 22:42
i've tried that but it's not working either i'm still getting the same error and its highlighting Range("A1").Select as the issue — alistair_Welles, Jul 24 '15 at 00:19

score 1 · Accepted Answer · edited May 23 '17 at 11:58

You need more than just a column label in A1 if you are going to use xlDown. There has to be at least one more value in column A or you will traverse to the bottom of the worksheet. It is usually better to look from the bottom of the worksheet upwards and then offset one row down.

With Sheets("Database")       'Primarily use Database worksheet
    If .FilterMode Then .ShowAllData
    With .Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) 'look from bottom up then down 1 row
        'direct value transfer is faster than Copy, Paste Special, Values
        .Cells.Resize(1, 8) = Sheets("Entry Form").Range("E10:L10").Value
    End With
End With

With Sheets("Entry Form")     'Primarily use Entry Form worksheet
    .Range("E10:L10").ClearContents 'Clears data from drop down selections
    .Range("E10").Select            'Returns selection back to Date entry box
End With

This makes use of the With ... End With statement to control which worksheet is receiving attention. See How to avoid using Select in Excel VBA macros for more methods on getting away from relying on select and activate to accomplish your goals.

OK so this worked. Could someone be kind enough to explain what each element means or direct me somewhere that I can learn to actually make these on my own? — alistair_Welles, Jul 24 '15 at 00:28

Excel VBA to copy

1 Answers1