Cutting information from 1 cell and pasting into the next free space in the column

[zilch4ry]

Hi, basically I have been asked to build a spreadsheet for a simple ICT project, which involves customers entering their information into an assigned cell, then a macro will be used to cut this information and place it in the next available cell, in the correct column on a separate worksheet. I'll be the first to say that i'm terrible at recording macros in EXCEL and if anybody could correct this code it will be hugely appreciated.

Sub newcustomer()
'
' newcustomer Macro
'

'
    Range("G9").Select
    Selection.Copy
    Sheets("Customer").Select
    Range("F7").Select
    ActiveSheet.Paste
    Sheets("New Member").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    Range("K9").Select
    Selection.Copy
    Sheets("Customer").Select
    Range("G7").Select
    ActiveSheet.Paste
    Sheets("New Member").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    Range("G12").Select
    Selection.Copy
    Sheets("Customer").Select
    Range("C7").Select
    ActiveSheet.Paste
    Sheets("New Member").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    Range("G13").Select
    Selection.Copy
    Sheets("Customer").Select
    Range("D7").Select
    ActiveSheet.Paste
    Sheets("New Member").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    Range("G14").Select
    Selection.Copy
    Sheets("Customer").Select
    Range("E7").Select
    ActiveSheet.Paste
    Sheets("New Member").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    Range("G15").Select
    Selection.Copy
    Sheets("Customer").Select
    Range("B7").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.ClearComments
    Sheets("New Member").Select
    Selection.ClearContents
End Sub

Basically all of the cells which have a 7 following them, for example "Range("F7").Select" need to be placed in the next available free space in column F, etc.

Thanks, Tom

 

[AlphaFrog]

Hi and welcome. Try something like this...

Sub newcustomer()

    '
    ' newcustomer Macro
    '

    Dim wsSource As Worksheet
    Dim wsDest As Worksheet
    Dim Nextrow As Long
    
    Set wsSource = Sheets("New Member")
    Set wsDest = Sheets("Customer")
    
    ' Next available row on Destination worksheet
    Nextrow = wsDest.Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row + 1

    With wsSource
    
        .Range("G9").Cut wsDest.Range("F" & Nextrow)
        .Range("K9").Cut wsDest.Range("G" & Nextrow)
        .Range("G12").Cut wsDest.Range("C" & Nextrow)
        .Range("G13").Cut wsDest.Range("D" & Nextrow)
        .Range("G14").Cut wsDest.Range("E" & Nextrow)
        .Range("G15").Cut wsDest.Range("B" & Nextrow)
        
    End With

End Sub

 

[zilch4ry]

Thank you very much! it did exactly what I wanted