Hi,

Is it possible to take the names and addresses from one spreadsheet and
Create a blank form and copy each persons information into the blank form
and then save the file and then jump to the next name and address and do the
same, until they are all created. I have about 87 names and addresses that I
want to export to a blank form that I will printout and put in a notebook so
I can keep a log of the service work I perform at each location. Is this
possible? If so how hard would it be to create? I have no VBA experience,
but I sure would like to learn and this would be a good place to start.


Thanks,

Stacey

Stacey

You can do it and it's not too hard. To be clear, you want 87 new
workbooks? That's a lot. And do you just want to print them out or would
you be using them later? If you just want a hard copy, you should consider
not creating new workbooks and instead just hiding all the rows but one,
then printing.

Sub PrintEachRow()

Dim cell As Range
Dim Rng As Range

Set Rng = Sheet1.Range("A1", Sheet1.Range("A1").End(xlDown))

For Each cell In Rng.Cells
Rng.EntireRow.Hidden = True
cell.EntireRow.Hidden = False
cell.Parent.PrintOut
Next cell

End Sub

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

Stacey wrote:
Hi,

Is it possible to take the names and addresses from one spreadsheet
and Create a blank form and copy each persons information into the blank
form and then save the file and then jump to the next name and address
and do the same, until they are all created. I have about 87 names and
addresses that I want to export to a blank form that I will printout and
put in a notebook so I can keep a log of the service work I perform at
each location. Is this possible? If so how hard would it be to create? I
have no VBA experience, but I sure would like to learn and this would be
a good place to start.


Thanks,

Stacey

Copy the code below. Open your workbook. Click Tools
Macros Visual Basic Editor. In the VBA window click
Insert Module and paste the code. I did the best I
could to explane it. That's the text in the VBA window
that's green. Hit your F8 key to step through the code
and watch what is does. For the most part it should be
understandable enough to modify the code for your
specific needs. It's not as big as it looks; most of it
is explanation.

Sub Macro1()
Dim WBName
WBName = ActiveWorkbook.Name
Range("A1").Select 'change A1 to whatever the first row
of names is

Do
Rows(ActiveCell.Row & ":" & ActiveCell.Row).Select
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
Range("A1").Select
'The next line will name the worksheet the name of
the person
'presumably the first and last names are not in the
same cell
'If they are then delete everything after
ActiveCell.value
'By the way the & sign brings two cell together
' For instance Joe(A1) Smith(B1) becomes JoeSmith
ActiveSheet.Name = ActiveCell.Value &
ActiveCell.Offset(, 1).Value

'The next set the variable WBName to the worksheet
name
'in this case the persons name so I can use it late on
'to save the workbook as the persons name
WBName = ActiveSheet.Name
Application.CutCopyMode = False

'Where it says "c:\temp\" you will need to change that
'to the directory you want to save you files to
'Make sure you put the path name in quotes just like
it is now
ActiveWorkbook.SaveAs Filename:= _
"C:\Temp\" & WBName & ".xls", _
FileFormat:=xlNormal, Password:="",
WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWorkbook.Close
ActiveCell.Offset(1).Activate
' The next line: do the loop until the Activecell has
nothing in it
Loop Until ActiveCell.Value = ""
End Sub

Jim,

When I tried to run this macro, I got an error message that said
"Compile Error : Invalid use of property" with the word "value" highlighted.

Sub Macro1()
Dim WBName
WBName = ActiveWorkbook.Name
Range("A1").Select

Do
Rows(ActiveCell.Row & ":" & ActiveCell.Row).Select
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
Range("A1").Select

ActiveCell.Value (This where I got the compile error)


Thank you for your time and help!


Stacey
"Jim" wrote in message
...
Copy the code below. Open your workbook. Click Tools
Macros Visual Basic Editor. In the VBA window click
Insert Module and paste the code. I did the best I
could to explane it. That's the text in the VBA window
that's green. Hit your F8 key to step through the code
and watch what is does. For the most part it should be
understandable enough to modify the code for your
specific needs. It's not as big as it looks; most of it
is explanation.

Sub Macro1()
Dim WBName
WBName = ActiveWorkbook.Name
Range("A1").Select 'change A1 to whatever the first row
of names is

Do
Rows(ActiveCell.Row & ":" & ActiveCell.Row).Select
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
Range("A1").Select
'The next line will name the worksheet the name of
the person
'presumably the first and last names are not in the
same cell
'If they are then delete everything after
ActiveCell.value
'By the way the & sign brings two cell together
' For instance Joe(A1) Smith(B1) becomes JoeSmith
ActiveSheet.Name = ActiveCell.Value &
ActiveCell.Offset(, 1).Value

'The next set the variable WBName to the worksheet
name
'in this case the persons name so I can use it late on
'to save the workbook as the persons name
WBName = ActiveSheet.Name
Application.CutCopyMode = False

'Where it says "c:\temp\" you will need to change that
'to the directory you want to save you files to
'Make sure you put the path name in quotes just like
it is now
ActiveWorkbook.SaveAs Filename:= _
"C:\Temp\" & WBName & ".xls", _
FileFormat:=xlNormal, Password:="",
WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWorkbook.Close
ActiveCell.Offset(1).Activate
' The next line: do the loop until the Activecell has
nothing in it
Loop Until ActiveCell.Value = ""
End Sub

Dick,
I guess I was kind of vague on what I wanted to do. I created a form in
one excel file and an Address list of the places I am called on to
provide service, in another excel file. What I wanted to do was take all
of the information from the excel file conatining the addresses and
paste it into the excel file with the form,Which is formatted like a
form and has some blanks for information not found in the Address List
excel file, ( Like serial #'s of equipment and location specific data).
It will then save the form with the name of Cell a1 + b1. It will then
go to the next row of information in the address excel file and repeat
the process for all of the remaining accounts. This way I will have a
seperate excel file for each account.I will then either print it out or
copy it to my PDA.

I hope this makes sense or at least more sense.

Can you recomend a good site or reference on VBA? This is something I
want to learn. I have a project I need to do using the same address file
and Map Point. I figured this would be a good place to get my feet wet.

Thank you so much for your help!!!!

Stacey



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Stacey

Without knowing your exact layout, it sounds like you need something like
this

Sub InsertAddress()

Dim wbAddress As Workbook
Dim wbForm As Workbook
Dim shAddress As Worksheet
Dim Rng As Range
Dim cell As Range

'create workbook and worksheet variables
Set wbAddress = Workbooks("Addresses.xls")
Set wbForm = Workbooks("MyForm.xls")
Set shAddress = wbAddress.Sheets(1)

'define range that is first column of address
Set Rng = shAddress.Range("a1", shAddress.Range("a1").End(xlDown))

'loop through the cells
For Each cell In Rng.Cells

'write the values from address to the form
With wbForm.Sheets(1)
.Range("a1").Value = cell.Value
.Range("a2").Value = cell.Offset(0, 1).Value
.Range("a3").Value = cell.Offset(0, 3).Value

'save a copy of the form
wbForm.SaveCopyAs "C:\" & .Range("a1").Value & _
.Range("b1").Value & ".xls"

End With

'process the next address
Next cell

End Sub

You'll need to change some of that to match your situation.


Can you recomend a good site or reference on VBA? This is something I
want to learn. I have a project I need to do using the same address file
and Map Point. I figured this would be a good place to get my feet wet.


The Spreadsheet Page has a list of links to sites

http://j-walk.com/ss/excel/links/index.htm

You can also check out my site

www.dicks-blog.com

The only thing I've seen on Map Point is on Chip's site

http://www.cpearson.com/excel/whatsnew.htm

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

Dick,

Thank you so much for your help! I tried Your macro, but the results
were not what I am looking for ( Because you didn't know the layout of
my ADDRESSES.XLS and MYFORM.XLS) I tried to edit the code, without
success. Here is what I am trying to do:

Put the Value of A2 from ADDRESS.XLS in A2 of MYFORM.XLS
Put the Value of C2 from ADDRESS.XLS in B2 of MYFORM.XLS
Put the Value of J2 from ADDRESS.XLS in D2 of MYFORM.XLS
Put the Value of Q2 from ADDRESS.XLS in E2 of MYFORM.XLS
Put the Value of R2 from ADDRESS.XLS in F2 of MYFORM.XLS
Put the Value of E2 from ADDRESS.XLS in A4 of MYFORM.XLS
Put the Value of F2 from ADDRESS.XLS in D4 of MYFORM.XLS
Put the Value of G2 from ADDRESS.XLS in E4 of MYFORM.XLS
Put the Value of H2 from ADDRESS.XLS in F4 of MYFORM.XLS

And then drop to the next row of the ADDRESS.XLS file and do the same
thing until it reaches any empty row.

I am going to order a book to help me figure out how to write VBA Code.

I Can't tell you how much I appreciate you taking the time to help me.

Stacey




*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Stacey

Try this

Sub InsertAddress()

Dim wbAddress As Workbook
Dim wbForm As Workbook
Dim Rng As Range
Dim cell As Range

'create workbook and worksheet variables
Set wbAddress = Workbooks("Address.xls")
Set wbForm = Workbooks("MyForm.xls")

'define range that is first column of address
With wbAddress.Sheets(1)
Set Rng = .Range("A2", .Range("A2").End(xlDown))
End With

'loop through the cells
For Each cell In Rng.Cells

'write the values from address to the form
With wbForm.Sheets(1)
.Range("a2").Value = cell.Value
.Range("b2").Value = cell.Offset(0, 3).Value 'D2
.Range("d2").Value = cell.Offset(0, 9).Value 'J2
.Range("e2").Value = cell.Offset(0, 16).Value 'Q2
.Range("f2").Value = cell.Offset(0, 17).Value 'R2
.Range("a4").Value = cell.Offset(0, 4).Value 'E2
.Range("d4").Value = cell.Offset(0, 5).Value 'F2
.Range("e4").Value = cell.Offset(0, 6).Value 'G2
.Range("f4").Value = cell.Offset(0, 7).Value 'H2

'save a copy of the form
wbForm.SaveCopyAs wbForm.Path & "\" & _
cell.Value & cell.Offset(0, 1).Value & _
".xls"

End With

'process the next address
Next cell

End Sub

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

"Stacey Trammel" wrote in message
...
Dick,

Thank you so much for your help! I tried Your macro, but the results
were not what I am looking for ( Because you didn't know the layout of
my ADDRESSES.XLS and MYFORM.XLS) I tried to edit the code, without
success. Here is what I am trying to do:

Put the Value of A2 from ADDRESS.XLS in A2 of MYFORM.XLS
Put the Value of C2 from ADDRESS.XLS in B2 of MYFORM.XLS
Put the Value of J2 from ADDRESS.XLS in D2 of MYFORM.XLS
Put the Value of Q2 from ADDRESS.XLS in E2 of MYFORM.XLS
Put the Value of R2 from ADDRESS.XLS in F2 of MYFORM.XLS
Put the Value of E2 from ADDRESS.XLS in A4 of MYFORM.XLS
Put the Value of F2 from ADDRESS.XLS in D4 of MYFORM.XLS
Put the Value of G2 from ADDRESS.XLS in E4 of MYFORM.XLS
Put the Value of H2 from ADDRESS.XLS in F4 of MYFORM.XLS

And then drop to the next row of the ADDRESS.XLS file and do the same
thing until it reaches any empty row.

I am going to order a book to help me figure out how to write VBA Code.

I Can't tell you how much I appreciate you taking the time to help me.

Stacey




*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Dick,


Thank You ! That worked Perfectly! Again Thank You!!!

Stacey

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!