How do I sort this Macro out, so it selects the next row automatically

Question

I am making a Movie Database on Excel, I have set it all up. Its working fine, i decided to add a Data entry form which will allow the user to input movie details in a form and automatically using a macro it would then move this data to a separate Worksheet with all my movies in. I have managed to record all this step and it works fine however it overwrites data and only uses the row that I pasted it to which was 'A47'. I now want to know how to edit the code so it changes to the next row if data is already available in this row. Another thing to note is that my macro also formats that selection, so that would need changing too. The formatting basically changes certain cells to be bold and text alignment. I will attach the code so you can see what I'm talking about. Also the code at the end deletes the data in the data entry form so its fresh for another entry.

Sorry I'm new to this all, I have looked around but no one has a similar problem as mine.

Any help would be appreciated.

Thanks

Sub SubmitMovie()
'
' SubmitMovie Macro
'

'
    Range("K9,K11,K13,K15,K17,K19,K21").Select
    Range("K21").Activate
    Selection.Copy
    Sheets("MovieList").Select
    Range("A74:G74").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    Range("B74").Select
    Application.CutCopyMode = False
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("D74").Select
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("A74:G74").Select
    Range("G74").Activate
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Sheets("Add New Movie").Select
    Range("K9").Select
    ActiveCell.FormulaR1C1 = ""
    Range("K11").Select
    ActiveCell.FormulaR1C1 = ""
    Range("K13").Select
    ActiveCell.FormulaR1C1 = ""
    Range("K15").Select
    ActiveCell.FormulaR1C1 = ""
    Range("K17").Select
    ActiveCell.FormulaR1C1 = ""
    Range("K19").Select
    ActiveCell.FormulaR1C1 = ""
    Range("K21").Select
    ActiveCell.FormulaR1C1 = ""
    Range("D28").Select
End Sub

score 0 · Accepted Answer · answered Feb 05 '14 at 22:44

Replace this

Range("K9,K11,K13,K15,K17,K19,K21").Select
Range("K21").Activate
Selection.Copy
Sheets("MovieList").Select
Range("A74:G74").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=True

with

Dim dest as Range

Activesheet.Range("K9,K11,K13,K15,K17,K19,K21").Copy

'find the first non-empty cell in ColA (from bottom up)    
Set dest = Sheets("MovieList").Cells(rows.count,1).End(xlUp).offset(1,0)

dest.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, _
      SkipBlanks:= False, Transpose:=True

Thanks, your answered worked. Appreciate all the help :) – ryounus95 Feb 06 '14 at 14:59 — ryounus95, Feb 06 '14 at 14:59

score 0 · Answer 2 · edited May 23 '17 at 12:20

Welcome to SO.

Since you are new to VBA you have chosen a good way to start learning more by using the macro recorder, but you have already learned that it has its limitations. It doesn't always do things in the most efficent way.

Some pointers on how to improve the script:

Remove all code that you don't know what it does. Most of it should be self explanatory, but if you don't know what it does, chances are you don't need it, because the macro recorder adds lots of uneccessary stuff.
Avoid using Select to navigate the worksheet. It is very inefficient and will slow down your code: tips on how to avoid using select.
There are lots of questions on SO about finding the last used row in order to know where new data can be saved.
Use Option Explicit at the top of each code module to minimize confusion and errors caused by typos etc. It will force you to explicitly declare all variables used, which is a good thing since VBA otherwise will accept all variable names as new variant-types if they haven't been declared before.
If you get stuck on a specific problem - ask questions on that specific problem.

score 0 · Answer 3 · edited May 23 '17 at 11:56

This previous post should help you see some of the concepts/syntax involved in the solution: Loops & Rows

The bottom line is you've run into an issue that macro recorder cannot get you out of. It would be really beneficial for you to take some time to learn about loops, counts and the Cells() function in VBA. Olle and Tim are spot on...especially Tim's "Set dest =" line.

This link shows a good example of loop syntax and may be a help to you on future problems of a similar nature:

Looping Through Ranges

How do I sort this Macro out, so it selects the next row automatically

3 Answers3