Need to copy noncontiguous values from one sheet to a row on a second sheet like logging a check into a check register

[General Ledger]

Dear All,

I have an Excel 2003 workbook that is much like a checkbook. One sheet is the Form, which is protected except for cells where the user enters data (Payee, Amount, Date, etc.). I am looking for a macro that will copy data from certain noncontiguous cells on the Form sheet to another sheet that will act like a check register (lists records horizontally).

For example, copy the value at Form B2 to Register A2, Form B6 to Register A3, Form B8 to Register A4, and so on. The source of the data will always be the same cells on the Form sheet. The destination of the values will be the first blank row on the Register sheet. As records are added to the Register, the next record will be on the next blank row.

I am guessing a solution might include:
1. Using a Named Range on the source cells (if you can do this for noncontiguous cells)
2. Writing the values of all the cells in the Named Range to an array (I never used arrays before)
3. Paste the values in the array to the destination row.

Thanks for your help,

GL

 

[jbeaucaire]

I would imagine a simple approach would be:

Option Explicit

Sub RecordEntry()
Dim NextRow As Long
Dim fSht As Worksheet, dSht As Worksheet

Set fSht = Sheets("Form")       'the sheet where the form is being filled in
Set dSht = Sheets("Data")       'the sheet to transfer items to

'Next empty row on the data sheet
    NextRow = dSht.Range("A" & Rows.Count).End(xlUp).Row + 1

'transfer items
    dSht.Range("A" & NextRow).Value = fSht.Range("B2").Value
    dSht.Range("B" & NextRow).Value = fSht.Range("B6").Value
    dSht.Range("C" & NextRow).Value = fSht.Range("B8").Value
    dSht.Range("D" & NextRow).Value = fSht.Range("B12").Value
    
'clear form
    fSht.Range("B2,B6,B8,B12").Value = ""

    Beep
End Sub

If you use name ranges on your form, you could replace the sections in red with your named ranges.