CODEWITHSUNDEEP
excelcopyvba
alistair_Welles
alistair_Welles

Reputation: 69

Excel VBA to copy

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.

Upvotes: 4

Views: 200

Answers (1)

user4039065
user4039065

Reputation:

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.

Upvotes: 1

Related Questions