Is it possible to export each row of data to a seperate work book

S

Stacey

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
 
D

Dick Kusleika

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
 
J

Jim

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
 
S

Stacey Trammel

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!
 
S

Stacey

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
 
D

Dick Kusleika

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.
Click to expand...

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
 
S

Stacey Trammel

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!
 
D

Dick Kusleika

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
 
S

Stacey Trammel

Dick,

The original file that I get my addresses from is updated daily and has
thousand's of addresses in it. Is it possible to use AUTOFILTER to
filter out just the addresses I want and then have the macro to look at
that data only. Right now it works on all the data.
Thank You again for your help!

Stacey



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

Dick Kusleika

Stacey

To skip those rows that are hidden with an autofilter, add an If block to
your loop.

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

If Not cell.EntireRow.Hidden Then

'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

End If
'process the next address
Next cell
 
S

Stacey

Dick,

When I turn on autofilter and Step through the macro it will jump from
"If Not cell.EntireRow.Hidden Then"
to "End If", skipping all of the code in between. When I turn off
Autofilter it goes back to processing all of the code.

Do you have any suggestions?


Thanks,

Stacey
 
D

Dick Kusleika

Stacey
When I turn on autofilter and Step through the macro it will jump from
"If Not cell.EntireRow.Hidden Then"
to "End If", skipping all of the code in between. When I turn off
Autofilter it goes back to processing all of the code.
Click to expand...

Right, that's what it should do. If the row is hidden, it should skip all
the code. If the row is visible, it will process the code in between. Put
a breakpoint on one of the lines of the "in between" code and run the macro
and you can see when it stops. Or are you saying that it never stops?
 
S

Stacey

Dick,

It skips all of the rows, it doesn't matter if they are hidden or
visible it skips them all. Until I remove the autofilter, then it will start
working again.

Thanks,

Stacey
 
D

Dick Kusleika

Stacey
It skips all of the rows, it doesn't matter if they are hidden or
visible it skips them all. Until I remove the autofilter, then it will start
working again.
Click to expand...

Hmmm. It works for me. What version of Excel are you using?
 
S

Stacey

Dick,

I am using Office 2003.

How hard would it be to Prompt the user for Their name and then use an if
/Then statement to only look at the rows that had their name in that Row of
data?

Thanks again for your help!!!

Stacey
 
D

Dick Kusleika

Stacey

Not hard. Is that what you were autofiltering on?

Dim sName as String

sName = InputBox("Enter your name")

If sName = "False" Then
Exit Sub
End If

For Each cell In Rng.Cells
If cell.Value = sName Then
'Do other stuff in the loop
End If
Next cell
 
S

Stacey

Dick,

I can't thank you enough for all of the time you have spent with helping
me!

When I put the new code in it still skips all of the code that fills in
myform and then saves it. It just keeps looping.

I even tried putting the code in the address.xls file and running it from
that file. It still does the same thing. I think I am doing something wrong.
When I open the original address.xls file it tells me I need to open it as
read only and asks if I want to open it as read only and I click no. Would
this be the cause of the looping.

The row in the address that conatins my name is L, if that makes any
difference on what cell the macro is using when I enter my name?

Thanks Again!!

Stacey
 
D

Dick Kusleika

Stacey

How do you feel about emailing the workbook to me? I think we can knock
this out for all time if we're looking at the same thing.
 
Top