I think the best way would be to use a 3rd sheet to save the data entered
from the input sheet in. You'll need some VBA (macro) code and an easy way
to run the macro. I've provided some sample code that could be used as the
basis to modify for your use.
Step 1 would be to put the code into your workbook and modify it to "fit"
your setup. To put the code into the book, open it and press [Alt]+[F11] to
open the VB editor. Then choose Insert --> Module to open a new code module.
Copy the code below and paste it into the module and edit the Const values
as needed. You can add more Const values for other data I haven't thought
of, delete ones you don't need and adjust the section of code that moves the
data to make it agree with your list of source addresses and destination
columns. After this you can close the VB Editor.
Step 2 is to set up an easy way to run the macro. You could use Tools -->
Macro --> Macros and pick it from the list, but that's clumsy if you're doing
a lot of data entry. So think about using a command button on the sheet:
From the main Excel menu choose View --> Toolbars and pick the Forms menu.
Create a command button on the sheet, and as soon as you've finished
"drawing" it on the sheet an "Assign Macro" dialog will open. Simply
highlight the macro's name and click the OK button in the dialog. Now when
you click that button, the information will transfer from the input sheet to
the list sheet. You can then change the text on your button to make more
sense ... the button should still be in edit mode and you can click in it to
edit the text on it. Once that's done, click any cell to stop editing it and
begin using it as required.
Hope this helps you get started. Here's the example code:
Sub SaveClientData()
'change these Const values to
'match the content and layout of
'your workbook
Const EntrySheetName = "Sheet1"
Const ListSheetName = "Sheet3"
'this list would be the cell
'addresses for entries on the
'Data entry sheet
'change/add/delete from the
'list as needed
Const srcLastNameCell = "A1"
Const srcFirstNameCell = "B1"
Const srcMidNameCell = "C1"
Const srcStreet1 = "A2"
Const srcCity = "B2"
Const srcState = "C2"
Const srcZip = "D2"
Const srcEmail = "A3"
Const srcPhone = "B3"
'this list would be a list of
'the columns to put the source
'data into on the client list sheet
Const destLNameCol = "A"
Const destFNameCol = "B"
Const destMNameCol = "C"
Const destStreetCol = "D"
Const destCityCol = "E"
Const destStateCol = "F"
Const destZipCol = "G"
Const destEmailCol = "H"
Const destPhoneCol = "I"
'end of user defined constants
Dim destLastRow As Long
Dim srcSheet As Worksheet
Dim destSheet As Worksheet
Set srcSheet = ThisWorkbook.Worksheets(EntrySheetName)
Set destSheet = ThisWorkbook.Worksheets(ListSheetName)
'find next available row based on last name entries
destLastRow = destSheet.Range(destLNameCol & _
Rows.Count).End(xlUp).Row + 1
'move the data
destSheet.Range(destLNameCol & destLastRow) = _
srcSheet.Range(srcLastNameCell)
destSheet.Range(destFNameCol & destLastRow) = _
srcSheet.Range(srcFirstNameCell)
destSheet.Range(destMNameCol & destLastRow) = _
srcSheet.Range(srcMidNameCell)
destSheet.Range(destStreetCol & destLastRow) = _
srcSheet.Range(srcStreet1)
destSheet.Range(destCityCol & destLastRow) = _
srcSheet.Range(srcCity)
destSheet.Range(destStateCol & destLastRow) = _
srcSheet.Range(srcState)
destSheet.Range(destZipCol & destLastRow) = _
srcSheet.Range(srcZip)
destSheet.Range(destEmailCol & destLastRow) = _
srcSheet.Range(srcEmail)
destSheet.Range(destPhoneCol & destLastRow) = _
srcSheet.Range(srcPhone)
'we are done with the move, do some housekeeping
Set srcSheet = Nothing
Set destSheet = Nothing
End Sub
