Edexcel - Miscrosoft Visual Basic help

[helraizer1]

In Excel I have a "Home" page with cells in which you insert:
Customer Name
Customer Type (drop down type for 'Standard', 'Business man' and 'Tour Group'.)
Nights Staying
Room Number (Drop Down for room numbers; from Page "Customers" in which the numbers are in a named range)
and Date Of Arrival

I have a basic VB code to copy the details from the Home page to the Customers page. However, at the moment it only copies them to one place in the Customers page.

How would I link them to the cells next to the named range (Customers page) where the number is the same as in the drop down 'Room Number'.

So for example someone selects room '7' in the drop down list, then writes their details. How would I get it so, when they press add (run the Macro) the details are pasted (or moved to) in, say, cell C13 which is next to number 7 from the range.. and therefore the same for every/any number that is in the drop-down, goes to the same number in the Customers page.

I hope that makes sense.

Sam

[helraizer1]

So far I have written:

 If Range("G28").Value = "1" Then
    Sheets("Home").Select
    Range("G19:I19").Select
    Selection.Copy
    Sheets("Customers").Select
    Range("C7").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
    xlNone, SkipBlanks:=False, Transpose:=False
    Sheets("Home").Select
    Range("G22").Select
    Selection.Copy
    Sheets("Customers").Select
    Range("F7").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
    xlNone, SkipBlanks:=False, Transpose:=False
    Sheets("Home").Select
    Range("G25").Select
    Selection.Copy
    Sheets("Customers").Select
    Range("J7").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
    xlNone, SkipBlanks:=False, Transpose:=False
    Sheets("Home").Select
    Range("G31").Select
    Selection.Copy
    Sheets("Customers").Select
    Range("H7").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
    xlNone, SkipBlanks:=False, Transpose:=False
        Exit Sub
    End If
 

So far this works but since there are 50 'rooms' + 7 other rooms in the drop down.. I will have to do this code 57 times over. Is there an easier way to get the number to correspond with the same number in the next page (details in first post)?

Thanks,

Sam

[Killer42]

While there are probably more elegant ways to achieve this, I'm not all that familiar with using Excel features from VBA. A simple brute-force method would be to use a FOR loop to run through the range of cells, and handle each one in turn.

The section of code which would have been repeated 57 times is then required only once, either within the loop of in a separate Sub or Function which you call from inside the loop.