#1   Report Post  
Posted to microsoft.public.excel.setup
external usenet poster
 
Posts: 1
Default excel 2003

I'm trying to create a spreadsheet with customer information, and I need to
print out a profile sheet where all the information gets feed into.

Action taken:
1. created a spreadsheet to input information.
2. created a second spreadsheet - formated for "printing" individual
customer info. The information from the first spreadsheet gets fed into this
second sheet.
This is working but...

My question is:
How can I keep the exisitng info and just go down the row, adding new
information?
In anthoer words...I'd like to keep a master list of customers at the same
time.

Coded on the 2nd sheet: =(spreadsheet1!A2) for last name,
=(spreadsheet1!A3) for first name and so on.

I'm very new to excel 2003 and any tips will help. Thank you.


  #2   Report Post  
Posted to microsoft.public.excel.setup
external usenet poster
 
Posts: 2,203
Default excel 2003

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


"keys" wrote:

I'm trying to create a spreadsheet with customer information, and I need to
print out a profile sheet where all the information gets feed into.

Action taken:
1. created a spreadsheet to input information.
2. created a second spreadsheet - formated for "printing" individual
customer info. The information from the first spreadsheet gets fed into this
second sheet.
This is working but...

My question is:
How can I keep the exisitng info and just go down the row, adding new
information?
In anthoer words...I'd like to keep a master list of customers at the same
time.

Coded on the 2nd sheet: =(spreadsheet1!A2) for last name,
=(spreadsheet1!A3) for first name and so on.

I'm very new to excel 2003 and any tips will help. Thank you.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel 2007 versus Excel 2003 & Excel 97-2003 fully compatible Bumpersnesty Excel Worksheet Functions 0 April 26th 10 09:44 PM
importing/linking data from an Access 2003 Query to an Excel 2003 PerryK Excel Discussion (Misc queries) 2 August 24th 09 07:06 PM
import Excel 2003 file into Outlook 2003 - NO NAMED RANGES?? lewisma9 Excel Discussion (Misc queries) 0 February 27th 07 01:23 AM
Copying Excel 2003 Selection into Outlook 2003 HTML E-Mail Message [email protected] Excel Discussion (Misc queries) 0 July 10th 06 03:07 PM
Excel 2003 Database Driver Visual FoxPro 7 on Server 2003. Cindy Winegarden Excel Discussion (Misc queries) 0 November 28th 04 01:07 AM


All times are GMT +1. The time now is 12:08 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"