ok, my title may not be accurate but I wasn't quite sure what else to call
it. I have a workbook created that is a page layout of a bunch of different
fields in various locations that will print on a single sheet of paper. I
work in a campus records department and this sheet (we'll call it workbook1)
will be used for our counselors to manually type student data into the blank
provided beside the field label. I have another excel file (we'll call it
workbook2) that actually has the data in it that the counselors need on
workbook1. Workbook2 will include anywhere from 50-300 student records that
are about 50 fields of data each. Now I know how to doa fields reference in
workbook1to tell it to grab data from a certain field in workbook2
(=[workbook2.xls]sheet1!A1). However, is there a way to do a mail merge of
sorts so that it would do this for each and every record in workbook2 so
that if workbook2 has 50 records, it provides 50 page with unique data to
that record?

Thanks!
Buster

Anybody????? I really need help with this.

"Bubba Gump" wrote in message
...
ok, my title may not be accurate but I wasn't quite sure what else to call
it. I have a workbook created that is a page layout of a bunch of
different fields in various locations that will print on a single sheet of
paper. I work in a campus records department and this sheet (we'll call it
workbook1) will be used for our counselors to manually type student data
into the blank provided beside the field label. I have another excel file
(we'll call it workbook2) that actually has the data in it that the
counselors need on workbook1. Workbook2 will include anywhere from 50-300
student records that are about 50 fields of data each. Now I know how to
doa fields reference in workbook1to tell it to grab data from a certain
field in workbook2 (=[workbook2.xls]sheet1!A1). However, is there a way to
do a mail merge of sorts so that it would do this for each and every
record in workbook2 so that if workbook2 has 50 records, it provides 50
page with unique data to that record?

Thanks!
Buster

Saved from a previous post:

First, you could have used your excel table as the source and created the form
in MSWord (where you might have been able to make a nicer form???).

You may want to read some tips for mailmerge.
http://www.mvps.org/dmcritchie/excel/mailmerg.htm
http://www.mvps.org/word/FAQs/MailMerge

The first is from David McRitchie and the second is by Beth Melton and Dave
Rado.

======
But you can do what you want in excel (since your form is done).

But one thing I would do is add a new column A to your data. Use that as an
indicator that this row should be printed--if something happens and you have to
reprint a few, there's no sense printing all the forms.

This is the code I used:

Option Explicit
Option Base 0
Sub testme()
Dim FormWks As Worksheet
Dim DataWks As Worksheet
Dim myRng As Range
Dim myCell As Range
Dim iCtr As Long
Dim myAddresses As Variant

Set FormWks = Worksheets("form")
Set DataWks = Worksheets("data")

myAddresses = Array("b2", "c3", "d6", "F12")

With DataWks
Set myRng = .Range("b2", .Cells(.Rows.Count, "B").End(xlUp))
End With

For Each myCell In myRng.Cells
With myCell
If IsEmpty(.Offset(0, -1)) Then
'do nothing
Else
.Offset(0, -1).ClearContents 'for the next time
For iCtr = LBound(myAddresses) To UBound(myAddresses)
FormWks.Range(myAddresses(iCtr)).Value _
= myCell.Offset(0, iCtr).Value
Next iCtr
Application.Calculate 'just in case
FormWks.PrintOut preview:=True
End If
End With
Next myCell
End Sub

You'll want to change these lines:

Set FormWks = Worksheets("form")
Set DataWks = Worksheets("data")

myAddresses = Array("b2", "b3", "g6", "F12")

To match your workbook.

Since I used column A as the indicator, this will put the value in column B in
B2, column C in B3, D in G6 and E in F12. Just keep adding more addresses--but
go in that natural order (left to right starting in column B).

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Bubba Gump wrote:

ok, my title may not be accurate but I wasn't quite sure what else to call
it. I have a workbook created that is a page layout of a bunch of different
fields in various locations that will print on a single sheet of paper. I
work in a campus records department and this sheet (we'll call it workbook1)
will be used for our counselors to manually type student data into the blank
provided beside the field label. I have another excel file (we'll call it
workbook2) that actually has the data in it that the counselors need on
workbook1. Workbook2 will include anywhere from 50-300 student records that
are about 50 fields of data each. Now I know how to doa fields reference in
workbook1to tell it to grab data from a certain field in workbook2
(=[workbook2.xls]sheet1!A1). However, is there a way to do a mail merge of
sorts so that it would do this for each and every record in workbook2 so
that if workbook2 has 50 records, it provides 50 page with unique data to
that record?

Thanks!
Buster

--

Dave Peterson

Dave, thanks for all of your help. Let me clarify. The code you put down
towards the bottom of your post: is that macro code then. I've always though
a macro to be hit record, do your key strokes, hit stop and there's your
macro. Also, so are you saying I can use my existing excel data file, and my
other existing excel worksheet along with a macro and create the merge? If
so, does this merge them to a printer or can it merge them to one big
multi-page new file like a word mail merge does?

Thanks again Dave!
Buster

"Dave Peterson" wrote in message
...
Saved from a previous post:

First, you could have used your excel table as the source and created the
form
in MSWord (where you might have been able to make a nicer form???).

You may want to read some tips for mailmerge.
http://www.mvps.org/dmcritchie/excel/mailmerg.htm
http://www.mvps.org/word/FAQs/MailMerge

The first is from David McRitchie and the second is by Beth Melton and
Dave
Rado.

======
But you can do what you want in excel (since your form is done).

But one thing I would do is add a new column A to your data. Use that as
an
indicator that this row should be printed--if something happens and you
have to
reprint a few, there's no sense printing all the forms.

This is the code I used:

Option Explicit
Option Base 0
Sub testme()
Dim FormWks As Worksheet
Dim DataWks As Worksheet
Dim myRng As Range
Dim myCell As Range
Dim iCtr As Long
Dim myAddresses As Variant

Set FormWks = Worksheets("form")
Set DataWks = Worksheets("data")

myAddresses = Array("b2", "c3", "d6", "F12")

With DataWks
Set myRng = .Range("b2", .Cells(.Rows.Count, "B").End(xlUp))
End With

For Each myCell In myRng.Cells
With myCell
If IsEmpty(.Offset(0, -1)) Then
'do nothing
Else
.Offset(0, -1).ClearContents 'for the next time
For iCtr = LBound(myAddresses) To UBound(myAddresses)
FormWks.Range(myAddresses(iCtr)).Value _
= myCell.Offset(0, iCtr).Value
Next iCtr
Application.Calculate 'just in case
FormWks.PrintOut preview:=True
End If
End With
Next myCell
End Sub

You'll want to change these lines:

Set FormWks = Worksheets("form")
Set DataWks = Worksheets("data")

myAddresses = Array("b2", "b3", "g6", "F12")

To match your workbook.

Since I used column A as the indicator, this will put the value in column
B in
B2, column C in B3, D in G6 and E in F12. Just keep adding more
addresses--but
go in that natural order (left to right starting in column B).

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Bubba Gump wrote:

ok, my title may not be accurate but I wasn't quite sure what else to
call
it. I have a workbook created that is a page layout of a bunch of
different
fields in various locations that will print on a single sheet of paper. I
work in a campus records department and this sheet (we'll call it
workbook1)
will be used for our counselors to manually type student data into the
blank
provided beside the field label. I have another excel file (we'll call it
workbook2) that actually has the data in it that the counselors need on
workbook1. Workbook2 will include anywhere from 50-300 student records
that
are about 50 fields of data each. Now I know how to doa fields reference
in
workbook1to tell it to grab data from a certain field in workbook2
(=[workbook2.xls]sheet1!A1). However, is there a way to do a mail merge
of
sorts so that it would do this for each and every record in workbook2 so
that if workbook2 has 50 records, it provides 50 page with unique data to
that record?

Thanks!
Buster

--

Dave Peterson

It plops the values from the input sheet into the form sheet. Then prints that
form sheet--actually, it does a print preview (nice for testing).



Bubba Gump wrote:

Dave, thanks for all of your help. Let me clarify. The code you put down
towards the bottom of your post: is that macro code then. I've always though
a macro to be hit record, do your key strokes, hit stop and there's your
macro. Also, so are you saying I can use my existing excel data file, and my
other existing excel worksheet along with a macro and create the merge? If
so, does this merge them to a printer or can it merge them to one big
multi-page new file like a word mail merge does?

Thanks again Dave!
Buster

"Dave Peterson" wrote in message
...
Saved from a previous post:

First, you could have used your excel table as the source and created the
form
in MSWord (where you might have been able to make a nicer form???).

You may want to read some tips for mailmerge.
http://www.mvps.org/dmcritchie/excel/mailmerg.htm
http://www.mvps.org/word/FAQs/MailMerge

The first is from David McRitchie and the second is by Beth Melton and
Dave
Rado.

======
But you can do what you want in excel (since your form is done).

But one thing I would do is add a new column A to your data. Use that as
an
indicator that this row should be printed--if something happens and you
have to
reprint a few, there's no sense printing all the forms.

This is the code I used:

Option Explicit
Option Base 0
Sub testme()
Dim FormWks As Worksheet
Dim DataWks As Worksheet
Dim myRng As Range
Dim myCell As Range
Dim iCtr As Long
Dim myAddresses As Variant

Set FormWks = Worksheets("form")
Set DataWks = Worksheets("data")

myAddresses = Array("b2", "c3", "d6", "F12")

With DataWks
Set myRng = .Range("b2", .Cells(.Rows.Count, "B").End(xlUp))
End With

For Each myCell In myRng.Cells
With myCell
If IsEmpty(.Offset(0, -1)) Then
'do nothing
Else
.Offset(0, -1).ClearContents 'for the next time
For iCtr = LBound(myAddresses) To UBound(myAddresses)
FormWks.Range(myAddresses(iCtr)).Value _
= myCell.Offset(0, iCtr).Value
Next iCtr
Application.Calculate 'just in case
FormWks.PrintOut preview:=True
End If
End With
Next myCell
End Sub

You'll want to change these lines:

Set FormWks = Worksheets("form")
Set DataWks = Worksheets("data")

myAddresses = Array("b2", "b3", "g6", "F12")

To match your workbook.

Since I used column A as the indicator, this will put the value in column
B in
B2, column C in B3, D in G6 and E in F12. Just keep adding more
addresses--but
go in that natural order (left to right starting in column B).

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Bubba Gump wrote:

ok, my title may not be accurate but I wasn't quite sure what else to
call
it. I have a workbook created that is a page layout of a bunch of
different
fields in various locations that will print on a single sheet of paper. I
work in a campus records department and this sheet (we'll call it
workbook1)
will be used for our counselors to manually type student data into the
blank
provided beside the field label. I have another excel file (we'll call it
workbook2) that actually has the data in it that the counselors need on
workbook1. Workbook2 will include anywhere from 50-300 student records
that
are about 50 fields of data each. Now I know how to doa fields reference
in
workbook1to tell it to grab data from a certain field in workbook2
(=[workbook2.xls]sheet1!A1). However, is there a way to do a mail merge
of
sorts so that it would do this for each and every record in workbook2 so
that if workbook2 has 50 records, it provides 50 page with unique data to
that record?

Thanks!
Buster

--

Dave Peterson

--

Dave Peterson

I don't understand this part:
You'll want to change these lines:
Set FormWks = Worksheets("testform.xls") I get an error here saying "Runtime error 9, subscript out of range"
Set DataWks = Worksheets("testdata.xls")

myAddresses = Array("b2", "b3", "g6", "F12")

To match your workbook.
I don't understand this part either. Why do the cells jump alla around to B3, G6, and F12? I created an empty column A. What do I need to do with that column A?
Since I used column A as the indicator, this will put the value in column B in
B2, column C in B3, D in G6 and E in F12. Just keep adding more addresses--but
go in that natural order (left to right starting in column B).


"Dave Peterson" wrote in message ...
Saved from a previous post:

First, you could have used your excel table as the source and created the form
in MSWord (where you might have been able to make a nicer form???).

You may want to read some tips for mailmerge.
http://www.mvps.org/dmcritchie/excel/mailmerg.htm
http://www.mvps.org/word/FAQs/MailMerge

The first is from David McRitchie and the second is by Beth Melton and Dave
Rado.

======
But you can do what you want in excel (since your form is done).

But one thing I would do is add a new column A to your data. Use that as an
indicator that this row should be printed--if something happens and you have to
reprint a few, there's no sense printing all the forms.

This is the code I used:

Option Explicit
Option Base 0
Sub testme()
Dim FormWks As Worksheet
Dim DataWks As Worksheet
Dim myRng As Range
Dim myCell As Range
Dim iCtr As Long
Dim myAddresses As Variant

Set FormWks = Worksheets("form")
Set DataWks = Worksheets("data")

myAddresses = Array("b2", "c3", "d6", "F12")

With DataWks
Set myRng = .Range("b2", .Cells(.Rows.Count, "B").End(xlUp))
End With

For Each myCell In myRng.Cells
With myCell
If IsEmpty(.Offset(0, -1)) Then
'do nothing
Else
.Offset(0, -1).ClearContents 'for the next time
For iCtr = LBound(myAddresses) To UBound(myAddresses)
FormWks.Range(myAddresses(iCtr)).Value _
= myCell.Offset(0, iCtr).Value
Next iCtr
Application.Calculate 'just in case
FormWks.PrintOut preview:=True
End If
End With
Next myCell
End Sub

You'll want to change these lines:

Set FormWks = Worksheets("form")
Set DataWks = Worksheets("data")

myAddresses = Array("b2", "b3", "g6", "F12")

To match your workbook.

Since I used column A as the indicator, this will put the value in column B in
B2, column C in B3, D in G6 and E in F12. Just keep adding more addresses--but
go in that natural order (left to right starting in column B).

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Bubba Gump wrote:

ok, my title may not be accurate but I wasn't quite sure what else to call
it. I have a workbook created that is a page layout of a bunch of different
fields in various locations that will print on a single sheet of paper. I
work in a campus records department and this sheet (we'll call it workbook1)
will be used for our counselors to manually type student data into the blank
provided beside the field label. I have another excel file (we'll call it
workbook2) that actually has the data in it that the counselors need on
workbook1. Workbook2 will include anywhere from 50-300 student records that
are about 50 fields of data each. Now I know how to doa fields reference in
workbook1to tell it to grab data from a certain field in workbook2
(=[workbook2.xls]sheet1!A1). However, is there a way to do a mail merge of
sorts so that it would do this for each and every record in workbook2 so
that if workbook2 has 50 records, it provides 50 page with unique data to
that record?

Thanks!
Buster

--

Dave Peterson

The FromWks variable represents a worksheet--not a workbook.

So use
set formwks = worksheets("whatevertheworksheetnameisthatholdsthe formhere")
and
set datawks = worksheets("whatevertheworksheetnameisthatholdsthe datahere")

I don't know where you're copying the data from the data worksheet into the form
worksheet.

change that line:
myAddresses = Array("b2", "b3", "g6", "F12")
to what you want populated in the Form worksheet.

Remember for each row in the Data worksheet, the value in column B will go to
the first address, the value in column c will go to the second address, and so
forth.

This is a text only newsgroup. It's better to post in plain text (not HTML) and
no attachments, too.



Bubba Gump wrote:

I don't understand this part:
You'll want to change these lines:
Set FormWks = Worksheets("testform.xls") I get an error here
saying "Runtime error 9, subscript out of range"
Set DataWks = Worksheets("testdata.xls")
myAddresses = Array("b2", "b3", "g6", "F12")

To match your workbook.
I don't understand this part either. Why do the cells jump alla around to B3,
G6, and F12? I created an empty column A. What do I need to do with that
column A?
Since I used column A as the indicator, this will put the value in column
B in
B2, column C in B3, D in G6 and E in F12. Just keep adding more
addresses--but
go in that natural order (left to right starting in column B).

"Dave Peterson" wrote in message
...
Saved from a previous post:

First, you could have used your excel table as the source and created the
form
in MSWord (where you might have been able to make a nicer form???).

You may want to read some tips for mailmerge.
http://www.mvps.org/dmcritchie/excel/mailmerg.htm
http://www.mvps.org/word/FAQs/MailMerge

The first is from David McRitchie and the second is by Beth Melton and Dave
Rado.

======
But you can do what you want in excel (since your form is done).

But one thing I would do is add a new column A to your data. Use that as an
indicator that this row should be printed--if something happens and you have
to
reprint a few, there's no sense printing all the forms.

This is the code I used:

Option Explicit
Option Base 0
Sub testme()
Dim FormWks As Worksheet
Dim DataWks As Worksheet
Dim myRng As Range
Dim myCell As Range
Dim iCtr As Long
Dim myAddresses As Variant

Set FormWks = Worksheets("form")
Set DataWks = Worksheets("data")

myAddresses = Array("b2", "c3", "d6", "F12")

With DataWks
Set myRng = .Range("b2", .Cells(.Rows.Count, "B").End(xlUp))
End With

For Each myCell In myRng.Cells
With myCell
If IsEmpty(.Offset(0, -1)) Then
'do nothing
Else
.Offset(0, -1).ClearContents 'for the next time
For iCtr = LBound(myAddresses) To UBound(myAddresses)
FormWks.Range(myAddresses(iCtr)).Value _
= myCell.Offset(0, iCtr).Value
Next iCtr
Application.Calculate 'just in case
FormWks.PrintOut preview:=True
End If
End With
Next myCell
End Sub

You'll want to change these lines:

Set FormWks = Worksheets("form")
Set DataWks = Worksheets("data")

myAddresses = Array("b2", "b3", "g6", "F12")

To match your workbook.

Since I used column A as the indicator, this will put the value in column B
in
B2, column C in B3, D in G6 and E in F12. Just keep adding more
addresses--but
go in that natural order (left to right starting in column B).

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Bubba Gump wrote:

ok, my title may not be accurate but I wasn't quite sure what else to call
it. I have a workbook created that is a page layout of a bunch of different
fields in various locations that will print on a single sheet of paper. I
work in a campus records department and this sheet (we'll call it
workbook1)
will be used for our counselors to manually type student data into the
blank
provided beside the field label. I have another excel file (we'll call it
workbook2) that actually has the data in it that the counselors need on
workbook1. Workbook2 will include anywhere from 50-300 student records that
are about 50 fields of data each. Now I know how to doa fields reference in
workbook1to tell it to grab data from a certain field in workbook2
(=[workbook2.xls]sheet1!A1). However, is there a way to do a mail merge of
sorts so that it would do this for each and every record in workbook2 so
that if workbook2 has 50 records, it provides 50 page with unique data to
that record?

Thanks!
Buster

--

Dave Peterson

--

Dave Peterson

Hmmm, I'm creating the xls file from FoxPro using a "save as" command. My
only option is type xls or type xl5, or xl8. The only difference is the
version number of excel it's exporting as. XL8 is Excel. XL8 is excel 5.0
and 97. XLS is everything prior. What I'm getting at is i don't know how to
make it do a worksheet rather than a book or even if i can.

can I in those quotes, name the book and then the sheet within in it like
("[testform.xls]sheet1")?

ok, as far as the array addresses, I think I understand now. so since I have
about 60 blanks to fill in on my form, I will have to have all of those 60
cell addresses referenced here, and in the correct order as in the data
file?

Another question about your original code. I notice at the top you
referenced "Dim FormWks As Worksheet" but a few lines down where you define
Set FormWks, it says "Worksheet" without the S on the end. Is this correct
or a typo?

Thanks again Dave!
Buster

"Dave Peterson" wrote in message
...
The FromWks variable represents a worksheet--not a workbook.

So use
set formwks = worksheets("whatevertheworksheetnameisthatholdsthe formhere")
and
set datawks = worksheets("whatevertheworksheetnameisthatholdsthe datahere")

I don't know where you're copying the data from the data worksheet into
the form
worksheet.

change that line:
myAddresses = Array("b2", "b3", "g6", "F12")
to what you want populated in the Form worksheet.

Remember for each row in the Data worksheet, the value in column B will go
to
the first address, the value in column c will go to the second address,
and so
forth.

This is a text only newsgroup. It's better to post in plain text (not
HTML) and
no attachments, too.



Bubba Gump wrote:

I don't understand this part:
You'll want to change these lines:
Set FormWks = Worksheets("testform.xls") I get an error here
saying "Runtime error 9, subscript out of range"
Set DataWks = Worksheets("testdata.xls")
myAddresses = Array("b2", "b3", "g6", "F12")

To match your workbook.
I don't understand this part either. Why do the cells jump alla around to
B3,
G6, and F12? I created an empty column A. What do I need to do with that
column A?
Since I used column A as the indicator, this will put the value in
column
B in
B2, column C in B3, D in G6 and E in F12. Just keep adding more
addresses--but
go in that natural order (left to right starting in column B).

"Dave Peterson" wrote in message
...
Saved from a previous post:

First, you could have used your excel table as the source and created
the
form
in MSWord (where you might have been able to make a nicer form???).

You may want to read some tips for mailmerge.
http://www.mvps.org/dmcritchie/excel/mailmerg.htm
http://www.mvps.org/word/FAQs/MailMerge

The first is from David McRitchie and the second is by Beth Melton and
Dave
Rado.

======
But you can do what you want in excel (since your form is done).

But one thing I would do is add a new column A to your data. Use that
as an
indicator that this row should be printed--if something happens and you
have
to
reprint a few, there's no sense printing all the forms.

This is the code I used:

Option Explicit
Option Base 0
Sub testme()
Dim FormWks As Worksheet
Dim DataWks As Worksheet
Dim myRng As Range
Dim myCell As Range
Dim iCtr As Long
Dim myAddresses As Variant

Set FormWks = Worksheets("form")
Set DataWks = Worksheets("data")

myAddresses = Array("b2", "c3", "d6", "F12")

With DataWks
Set myRng = .Range("b2", .Cells(.Rows.Count, "B").End(xlUp))
End With

For Each myCell In myRng.Cells
With myCell
If IsEmpty(.Offset(0, -1)) Then
'do nothing
Else
.Offset(0, -1).ClearContents 'for the next time
For iCtr = LBound(myAddresses) To UBound(myAddresses)
FormWks.Range(myAddresses(iCtr)).Value _
= myCell.Offset(0, iCtr).Value
Next iCtr
Application.Calculate 'just in case
FormWks.PrintOut preview:=True
End If
End With
Next myCell
End Sub

You'll want to change these lines:

Set FormWks = Worksheets("form")
Set DataWks = Worksheets("data")

myAddresses = Array("b2", "b3", "g6", "F12")

To match your workbook.

Since I used column A as the indicator, this will put the value in
column B
in
B2, column C in B3, D in G6 and E in F12. Just keep adding more
addresses--but
go in that natural order (left to right starting in column B).

If you're new to macros, you may want to read David McRitchie's intro
at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Bubba Gump wrote:

ok, my title may not be accurate but I wasn't quite sure what else to
call
it. I have a workbook created that is a page layout of a bunch of
different
fields in various locations that will print on a single sheet of
paper. I
work in a campus records department and this sheet (we'll call it
workbook1)
will be used for our counselors to manually type student data into the
blank
provided beside the field label. I have another excel file (we'll call
it
workbook2) that actually has the data in it that the counselors need
on
workbook1. Workbook2 will include anywhere from 50-300 student records
that
are about 50 fields of data each. Now I know how to doa fields
reference in
workbook1to tell it to grab data from a certain field in workbook2
(=[workbook2.xls]sheet1!A1). However, is there a way to do a mail
merge of
sorts so that it would do this for each and every record in workbook2
so
that if workbook2 has 50 records, it provides 50 page with unique data
to
that record?

Thanks!
Buster

--

Dave Peterson

--

Dave Peterson

You can be specific about which workbook contains the worksheet.

Set FormWks = workbooks("testform.xls").Worksheets("sheet1")
Set DataWks = workbooks("anothernamehere.xls").Worksheets("data" )
or
Set DataWks = activeworkbook.Worksheets("data")
(the workbook that is active in excel's window)
or
Set DataWks = ThisWorkbook.Worksheets("data")
(the workbook that actually holds the code)

But whatever workbooks you use have to be already open for this to work.

=======
Take a look at that code once more.

Dim FormWks As Worksheet
Dim DataWks As Worksheet
....
Set FormWks = Worksheets("form")
Set DataWks = Worksheets("data")

FormWks ans DataWks are each a worksheet.

But when I do the assigment, I want to look at all the worksheets and use the
one named "Data" or "form".

(I didn't see any missing/extra s's in there.)

And yep with the addresses. I figured that the layout of the form would be kind
of "free-format". You'd be plopping information all over the worksheet--with no
rhyme or reason--except that it looks nice that way.

Was I wrong?


Bubba Gump wrote:

Hmmm, I'm creating the xls file from FoxPro using a "save as" command. My
only option is type xls or type xl5, or xl8. The only difference is the
version number of excel it's exporting as. XL8 is Excel. XL8 is excel 5.0
and 97. XLS is everything prior. What I'm getting at is i don't know how to
make it do a worksheet rather than a book or even if i can.

can I in those quotes, name the book and then the sheet within in it like
("[testform.xls]sheet1")?

ok, as far as the array addresses, I think I understand now. so since I have
about 60 blanks to fill in on my form, I will have to have all of those 60
cell addresses referenced here, and in the correct order as in the data
file?

Another question about your original code. I notice at the top you
referenced "Dim FormWks As Worksheet" but a few lines down where you define
Set FormWks, it says "Worksheet" without the S on the end. Is this correct
or a typo?

Thanks again Dave!
Buster

"Dave Peterson" wrote in message
...
The FromWks variable represents a worksheet--not a workbook.

So use
set formwks = worksheets("whatevertheworksheetnameisthatholdsthe formhere")
and
set datawks = worksheets("whatevertheworksheetnameisthatholdsthe datahere")

I don't know where you're copying the data from the data worksheet into
the form
worksheet.

change that line:
myAddresses = Array("b2", "b3", "g6", "F12")
to what you want populated in the Form worksheet.

Remember for each row in the Data worksheet, the value in column B will go
to
the first address, the value in column c will go to the second address,
and so
forth.

This is a text only newsgroup. It's better to post in plain text (not
HTML) and
no attachments, too.



Bubba Gump wrote:

I don't understand this part:
You'll want to change these lines:
Set FormWks = Worksheets("testform.xls") I get an error here
saying "Runtime error 9, subscript out of range"
Set DataWks = Worksheets("testdata.xls")
myAddresses = Array("b2", "b3", "g6", "F12")

To match your workbook.
I don't understand this part either. Why do the cells jump alla around to
B3,
G6, and F12? I created an empty column A. What do I need to do with that
column A?
Since I used column A as the indicator, this will put the value in
column
B in
B2, column C in B3, D in G6 and E in F12. Just keep adding more
addresses--but
go in that natural order (left to right starting in column B).

"Dave Peterson" wrote in message
...
Saved from a previous post:

First, you could have used your excel table as the source and created
the
form
in MSWord (where you might have been able to make a nicer form???).

You may want to read some tips for mailmerge.
http://www.mvps.org/dmcritchie/excel/mailmerg.htm
http://www.mvps.org/word/FAQs/MailMerge

The first is from David McRitchie and the second is by Beth Melton and
Dave
Rado.

======
But you can do what you want in excel (since your form is done).

But one thing I would do is add a new column A to your data. Use that
as an
indicator that this row should be printed--if something happens and you
have
to
reprint a few, there's no sense printing all the forms.

This is the code I used:

Option Explicit
Option Base 0
Sub testme()
Dim FormWks As Worksheet
Dim DataWks As Worksheet
Dim myRng As Range
Dim myCell As Range
Dim iCtr As Long
Dim myAddresses As Variant

Set FormWks = Worksheets("form")
Set DataWks = Worksheets("data")

myAddresses = Array("b2", "c3", "d6", "F12")

With DataWks
Set myRng = .Range("b2", .Cells(.Rows.Count, "B").End(xlUp))
End With

For Each myCell In myRng.Cells
With myCell
If IsEmpty(.Offset(0, -1)) Then
'do nothing
Else
.Offset(0, -1).ClearContents 'for the next time
For iCtr = LBound(myAddresses) To UBound(myAddresses)
FormWks.Range(myAddresses(iCtr)).Value _
= myCell.Offset(0, iCtr).Value
Next iCtr
Application.Calculate 'just in case
FormWks.PrintOut preview:=True
End If
End With
Next myCell
End Sub

You'll want to change these lines:

Set FormWks = Worksheets("form")
Set DataWks = Worksheets("data")

myAddresses = Array("b2", "b3", "g6", "F12")

To match your workbook.

Since I used column A as the indicator, this will put the value in
column B
in
B2, column C in B3, D in G6 and E in F12. Just keep adding more
addresses--but
go in that natural order (left to right starting in column B).

If you're new to macros, you may want to read David McRitchie's intro
at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Bubba Gump wrote:

ok, my title may not be accurate but I wasn't quite sure what else to
call
it. I have a workbook created that is a page layout of a bunch of
different
fields in various locations that will print on a single sheet of
paper. I
work in a campus records department and this sheet (we'll call it
workbook1)
will be used for our counselors to manually type student data into the
blank
provided beside the field label. I have another excel file (we'll call
it
workbook2) that actually has the data in it that the counselors need
on
workbook1. Workbook2 will include anywhere from 50-300 student records
that
are about 50 fields of data each. Now I know how to doa fields
reference in
workbook1to tell it to grab data from a certain field in workbook2
(=[workbook2.xls]sheet1!A1). However, is there a way to do a mail
merge of
sorts so that it would do this for each and every record in workbook2
so
that if workbook2 has 50 records, it provides 50 page with unique data
to
that record?

Thanks!
Buster

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson