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

Active Member
Joined
Dec 31, 2007
Messages
460
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
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

jbeaucaire

Well-known Member
Joined
May 8, 2002
Messages
6,012
I would imagine a simple approach would be:
Rich (BB code):
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.
 

Forum statistics

Threads
1,136,331
Messages
5,675,156
Members
419,552
Latest member
jsanjur

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top