mail merging 2 workbooks???

  • Thread starter Thread starter Bubba Gump
  • Start date Start date
B

Bubba Gump

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 said:
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
Click to expand...
 
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 said:
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 said:
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
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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
Click to expand...
Click to expand...
 
The FromWks variable represents a worksheet--not a workbook.

So use
set formwks = worksheets("whatevertheworksheetnameisthatholdstheformhere")
and
set datawks = worksheets("whatevertheworksheetnameisthatholdsthedatahere")

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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
The FromWks variable represents a worksheet--not a workbook.

So use
set formwks = worksheets("whatevertheworksheetnameisthatholdstheformhere")
and
set datawks = worksheets("whatevertheworksheetnameisthatholdsthedatahere")

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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
The FromWks variable represents a worksheet--not a workbook.

So use
set formwks = worksheets("whatevertheworksheetnameisthatholdstheformhere")
and
set datawks = worksheets("whatevertheworksheetnameisthatholdsthedatahere")

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).

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
Click to expand...
Click to expand...
Click to expand...
 
well "no rhyme or reason" is inaccurate in that the data is arranged with
data similar in nature. However, it's not just sitting in straight
columns/rows and there are merged cells all over hell and creation depending
on the size of the element that is in that position. does that make sense?

I'm gonna play with this this afternoon and see what I get. you say the
workbooks have to already be open? why is that? will it not open them if
they're in the same folder? In other words, if i open the form workbook and
run the macro, will it not open the data file if it resides in the same
folder as the form file? if not, is there a macro comman that will open it?

Thanks dave!

Dave Peterson said:
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 said:
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 said:
The FromWks variable represents a worksheet--not a workbook.

So use
set formwks =
worksheets("whatevertheworksheetnameisthatholdstheformhere")
and
set datawks =
worksheets("whatevertheworksheetnameisthatholdsthedatahere")

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).

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
Click to expand...
Click to expand...
Click to expand...
 
If the workbook isn't open, then excel can't find the stuff it needs.

Since you could have lots of different workbooks that are named testform.xls (in
lots of different folders, in different shared drives, on different CDs, on
different USB devices, ....) excel wouldn't know which one to open.

If you want to have excel open your workbooks, you can do:

Set FormWks _
= workbooks.open(filename:="C:\my documents\excel\testform.xls") _
.Worksheets("sheet1")

But then this expects the workbook to be closed!

You could have the code check.

dim wkbkF as workbook

set wkbkF = nothing
on error resume next
set wkbkf = workbooks("TestForm.xls")
on error goto 0

if wkbkf is nothing then
'it's not open
set wkbkf = workbooks.open(filename:="C:\my documents\excel\testform.xls")
end if

set formwks = wkbkf.worksheets("Sheet1")



Bubba said:
well "no rhyme or reason" is inaccurate in that the data is arranged with
data similar in nature. However, it's not just sitting in straight
columns/rows and there are merged cells all over hell and creation depending
on the size of the element that is in that position. does that make sense?

I'm gonna play with this this afternoon and see what I get. you say the
workbooks have to already be open? why is that? will it not open them if
they're in the same folder? In other words, if i open the form workbook and
run the macro, will it not open the data file if it resides in the same
folder as the form file? if not, is there a macro comman that will open it?

Thanks dave!

Dave Peterson said:
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 said:
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

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

So use
set formwks =
worksheets("whatevertheworksheetnameisthatholdstheformhere")
and
set datawks =
worksheets("whatevertheworksheetnameisthatholdsthedatahere")

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).

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
Click to expand...
Click to expand...
Click to expand...
 
ok, this is what I have and it bombs on the set myRng line. "Run-time error
'91': Object variable or With block variable not set." Man you're gonna wish
this thread had never surfaced.

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
Dim wkbkF As Workbook

Set wkbkF = Nothing
On Error Resume Next
Set wkbkF = Workbooks("Test0607Returning.xls")
On Error GoTo 0

If wkbkF Is Nothing Then
'it's not open
Set wkbkF = Workbooks.Open(Filename:="G:\FinPlan\Reports\Automated
Awd Wkshts\Test0607Returning.xls")
End If

Dim wkbkG As Workbook

Set wkbkG = Nothing
On Error Resume Next
Set wkbkG = Workbooks("c07ret_07a.xls")
On Error GoTo 0

If wkbkG Is Nothing Then
'it's not open
Set wkbkG = Workbooks.Open(Filename:="G:\FinPlan\Reports\Automated
Awd Wkshts\c07ret_07a.xls")
End If
Set FormWks = wkbkG.Worksheets("c07ret_07a")

myAddresses = Array("j4", "d4", "d10", "d11", "d7", "i16", "i19", "i21",
"i27")

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




Dave Peterson said:
If the workbook isn't open, then excel can't find the stuff it needs.

Since you could have lots of different workbooks that are named
testform.xls (in
lots of different folders, in different shared drives, on different CDs,
on
different USB devices, ....) excel wouldn't know which one to open.

If you want to have excel open your workbooks, you can do:

Set FormWks _
= workbooks.open(filename:="C:\my documents\excel\testform.xls") _
.Worksheets("sheet1")

But then this expects the workbook to be closed!

You could have the code check.

dim wkbkF as workbook

set wkbkF = nothing
on error resume next
set wkbkf = workbooks("TestForm.xls")
on error goto 0

if wkbkf is nothing then
'it's not open
set wkbkf = workbooks.open(filename:="C:\my
documents\excel\testform.xls")
end if

set formwks = wkbkf.worksheets("Sheet1")



Bubba said:
well "no rhyme or reason" is inaccurate in that the data is arranged with
data similar in nature. However, it's not just sitting in straight
columns/rows and there are merged cells all over hell and creation
depending
on the size of the element that is in that position. does that make
sense?

I'm gonna play with this this afternoon and see what I get. you say the
workbooks have to already be open? why is that? will it not open them if
they're in the same folder? In other words, if i open the form workbook
and
run the macro, will it not open the data file if it resides in the same
folder as the form file? if not, is there a macro comman that will open
it?

Thanks dave!

Dave Peterson said:
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

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

So use
set formwks =
worksheets("whatevertheworksheetnameisthatholdstheformhere")
and
set datawks =
worksheets("whatevertheworksheetnameisthatholdsthedatahere")

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).

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
Click to expand...
Click to expand...
Click to expand...
 
I found one problem with this in looking back at it but it didn't fix the
problem. After the endif following "Set wkbkF", i forgot to include the line
"Set FormWks = wkbkF.Worksheets("Sheet1")".

In case you notice on the line of mine that has the worksheet name as
c07ret_07a, FoxPro names the sheet the same thing as the file when it
exports the file to Excel.

Bubba Gump said:
ok, this is what I have and it bombs on the set myRng line. "Run-time
error '91': Object variable or With block variable not set." Man you're
gonna wish this thread had never surfaced.

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
Dim wkbkF As Workbook

Set wkbkF = Nothing
On Error Resume Next
Set wkbkF = Workbooks("Test0607Returning.xls")
On Error GoTo 0

If wkbkF Is Nothing Then
'it's not open
Set wkbkF = Workbooks.Open(Filename:="G:\FinPlan\Reports\Automated
Awd Wkshts\Test0607Returning.xls")
End If

Dim wkbkG As Workbook

Set wkbkG = Nothing
On Error Resume Next
Set wkbkG = Workbooks("c07ret_07a.xls")
On Error GoTo 0

If wkbkG Is Nothing Then
'it's not open
Set wkbkG = Workbooks.Open(Filename:="G:\FinPlan\Reports\Automated
Awd Wkshts\c07ret_07a.xls")
End If
Set FormWks = wkbkG.Worksheets("c07ret_07a")

myAddresses = Array("j4", "d4", "d10", "d11", "d7", "i16", "i19",
"i21", "i27")

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




Dave Peterson said:
If the workbook isn't open, then excel can't find the stuff it needs.

Since you could have lots of different workbooks that are named
testform.xls (in
lots of different folders, in different shared drives, on different CDs,
on
different USB devices, ....) excel wouldn't know which one to open.

If you want to have excel open your workbooks, you can do:

Set FormWks _
= workbooks.open(filename:="C:\my documents\excel\testform.xls") _
.Worksheets("sheet1")

But then this expects the workbook to be closed!

You could have the code check.

dim wkbkF as workbook

set wkbkF = nothing
on error resume next
set wkbkf = workbooks("TestForm.xls")
on error goto 0

if wkbkf is nothing then
'it's not open
set wkbkf = workbooks.open(filename:="C:\my
documents\excel\testform.xls")
end if

set formwks = wkbkf.worksheets("Sheet1")



Bubba said:
well "no rhyme or reason" is inaccurate in that the data is arranged
with
data similar in nature. However, it's not just sitting in straight
columns/rows and there are merged cells all over hell and creation
depending
on the size of the element that is in that position. does that make
sense?

I'm gonna play with this this afternoon and see what I get. you say the
workbooks have to already be open? why is that? will it not open them if
they're in the same folder? In other words, if i open the form workbook
and
run the macro, will it not open the data file if it resides in the same
folder as the form file? if not, is there a macro comman that will open
it?

Thanks dave!

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

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

So use
set formwks =
worksheets("whatevertheworksheetnameisthatholdstheformhere")
and
set datawks =
worksheets("whatevertheworksheetnameisthatholdsthedatahere")

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).

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
Click to expand...
Click to expand...
Click to expand...
 
When you do this stuff, is there only one worksheet in each of those workbooks?

If there's only one worksheet, you can just use the first (and only) worksheet
in the workbook and avoid having to use the names.

Option Explicit
Option Base 0
Sub testme()
Dim WkbkF As Workbook
Dim WkbkD As Workbook
Dim FormWks As Worksheet
Dim DataWks As Worksheet
Dim WkbkFName As String
Dim WkbkDName As String

Dim myRng As Range
Dim myCell As Range
Dim iCtr As Long
Dim myAddresses As Variant

WkbkFName = "Test0607Returning.xls"
Set WkbkF = Nothing
On Error Resume Next
Set WkbkF = Workbooks(WkbkFName)
On Error GoTo 0

If WkbkF Is Nothing Then
'it's not open
Set WkbkF = Workbooks.Open _
(Filename:="G:\FinPlan\Reports\Automated Awd Wkshts\" _
& WkbkFName)
End If
Set FormWks = WkbkF.Worksheets(1)

WkbkDName = "c07ret_07a.xls"

Set WkbkD = Nothing
On Error Resume Next
Set WkbkD = Workbooks(WkbkDName)
On Error GoTo 0

If WkbkD Is Nothing Then
'it's not open
Set WkbkD = Workbooks.Open _
(Filename:="G:\FinPlan\Reports\Automated Awd Wkshts\" _
& WkbkDName)
End If
Set DataWks = WkbkD.Worksheets(1)

myAddresses = Array("j4", "d4", "d10", "d11", "d7", "i16", _
"i19", "i21", "i27")

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

I changed the wkbkF and wkbkG names to wkbkF and wkbkD (F=Form, D=data) to make
it easier to know which represents which. Make sure I did the change correctly!

And if I can always use the first workbook (left most when you're looking at it
in excel), you can use:
Set FormWks = WkbkF.Worksheets(1)
instead of specifying the name.

When you get that error message:
error '91': Object variable or With block variable not set.
on this line:
with DataWks

It's because DataWks hasn't be initialized correctly. It could be because of a
few things that may have been fixed (now) (worksheets(1) may have fixed it).

And just think how dangerous you're becoming learning all this stuff.

Remember to save often!

(and get rid of that Preview:=true when you're ready for paper.)



Bubba said:
I found one problem with this in looking back at it but it didn't fix the
problem. After the endif following "Set wkbkF", i forgot to include the line
"Set FormWks = wkbkF.Worksheets("Sheet1")".

In case you notice on the line of mine that has the worksheet name as
c07ret_07a, FoxPro names the sheet the same thing as the file when it
exports the file to Excel.

Bubba Gump said:
ok, this is what I have and it bombs on the set myRng line. "Run-time
error '91': Object variable or With block variable not set." Man you're
gonna wish this thread had never surfaced.

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
Dim wkbkF As Workbook

Set wkbkF = Nothing
On Error Resume Next
Set wkbkF = Workbooks("Test0607Returning.xls")
On Error GoTo 0

If wkbkF Is Nothing Then
'it's not open
Set wkbkF = Workbooks.Open(Filename:="G:\FinPlan\Reports\Automated
Awd Wkshts\Test0607Returning.xls")
End If

Dim wkbkG As Workbook

Set wkbkG = Nothing
On Error Resume Next
Set wkbkG = Workbooks("c07ret_07a.xls")
On Error GoTo 0

If wkbkG Is Nothing Then
'it's not open
Set wkbkG = Workbooks.Open(Filename:="G:\FinPlan\Reports\Automated
Awd Wkshts\c07ret_07a.xls")
End If
Set FormWks = wkbkG.Worksheets("c07ret_07a")

myAddresses = Array("j4", "d4", "d10", "d11", "d7", "i16", "i19",
"i21", "i27")

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




Dave Peterson said:
If the workbook isn't open, then excel can't find the stuff it needs.

Since you could have lots of different workbooks that are named
testform.xls (in
lots of different folders, in different shared drives, on different CDs,
on
different USB devices, ....) excel wouldn't know which one to open.

If you want to have excel open your workbooks, you can do:

Set FormWks _
= workbooks.open(filename:="C:\my documents\excel\testform.xls") _
.Worksheets("sheet1")

But then this expects the workbook to be closed!

You could have the code check.

dim wkbkF as workbook

set wkbkF = nothing
on error resume next
set wkbkf = workbooks("TestForm.xls")
on error goto 0

if wkbkf is nothing then
'it's not open
set wkbkf = workbooks.open(filename:="C:\my
documents\excel\testform.xls")
end if

set formwks = wkbkf.worksheets("Sheet1")



Bubba Gump wrote:

well "no rhyme or reason" is inaccurate in that the data is arranged
with
data similar in nature. However, it's not just sitting in straight
columns/rows and there are merged cells all over hell and creation
depending
on the size of the element that is in that position. does that make
sense?

I'm gonna play with this this afternoon and see what I get. you say the
workbooks have to already be open? why is that? will it not open them if
they're in the same folder? In other words, if i open the form workbook
and
run the macro, will it not open the data file if it resides in the same
folder as the form file? if not, is there a macro comman that will open
it?

Thanks dave!

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

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

So use
set formwks =
worksheets("whatevertheworksheetnameisthatholdstheformhere")
and
set datawks =
worksheets("whatevertheworksheetnameisthatholdsthedatahere")

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).

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
Click to expand...
Click to expand...
Click to expand...
 
ok, to answer your question, yes it is always the first sheet but it looks
like you went ahead and included that in this latest version of the code. I
ran the macro and got no errors, but at the same time, it didn't seem to do
anything. I have the form file open, did alt-f8 and selected the testme
macro and ran it. It opened up the data file and then i couldn't tell that
anything else happened. Am I missing something?

Thanks!
Buster

Dave Peterson said:
When you do this stuff, is there only one worksheet in each of those
workbooks?

If there's only one worksheet, you can just use the first (and only)
worksheet
in the workbook and avoid having to use the names.

Option Explicit
Option Base 0
Sub testme()
Dim WkbkF As Workbook
Dim WkbkD As Workbook
Dim FormWks As Worksheet
Dim DataWks As Worksheet
Dim WkbkFName As String
Dim WkbkDName As String

Dim myRng As Range
Dim myCell As Range
Dim iCtr As Long
Dim myAddresses As Variant

WkbkFName = "Test0607Returning.xls"
Set WkbkF = Nothing
On Error Resume Next
Set WkbkF = Workbooks(WkbkFName)
On Error GoTo 0

If WkbkF Is Nothing Then
'it's not open
Set WkbkF = Workbooks.Open _
(Filename:="G:\FinPlan\Reports\Automated Awd
Wkshts\" _
& WkbkFName)
End If
Set FormWks = WkbkF.Worksheets(1)

WkbkDName = "c07ret_07a.xls"

Set WkbkD = Nothing
On Error Resume Next
Set WkbkD = Workbooks(WkbkDName)
On Error GoTo 0

If WkbkD Is Nothing Then
'it's not open
Set WkbkD = Workbooks.Open _
(Filename:="G:\FinPlan\Reports\Automated Awd
Wkshts\" _
& WkbkDName)
End If
Set DataWks = WkbkD.Worksheets(1)

myAddresses = Array("j4", "d4", "d10", "d11", "d7", "i16", _
"i19", "i21", "i27")

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

I changed the wkbkF and wkbkG names to wkbkF and wkbkD (F=Form, D=data) to
make
it easier to know which represents which. Make sure I did the change
correctly!

And if I can always use the first workbook (left most when you're looking
at it
in excel), you can use:
Set FormWks = WkbkF.Worksheets(1)
instead of specifying the name.

When you get that error message:
error '91': Object variable or With block variable not set.
on this line:
with DataWks

It's because DataWks hasn't be initialized correctly. It could be because
of a
few things that may have been fixed (now) (worksheets(1) may have fixed
it).

And just think how dangerous you're becoming learning all this stuff.

Remember to save often!

(and get rid of that Preview:=true when you're ready for paper.)



Bubba said:
I found one problem with this in looking back at it but it didn't fix the
problem. After the endif following "Set wkbkF", i forgot to include the
line
"Set FormWks = wkbkF.Worksheets("Sheet1")".

In case you notice on the line of mine that has the worksheet name as
c07ret_07a, FoxPro names the sheet the same thing as the file when it
exports the file to Excel.

Bubba Gump said:
ok, this is what I have and it bombs on the set myRng line. "Run-time
error '91': Object variable or With block variable not set." Man you're
gonna wish this thread had never surfaced.

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
Dim wkbkF As Workbook

Set wkbkF = Nothing
On Error Resume Next
Set wkbkF = Workbooks("Test0607Returning.xls")
On Error GoTo 0

If wkbkF Is Nothing Then
'it's not open
Set wkbkF =
Workbooks.Open(Filename:="G:\FinPlan\Reports\Automated
Awd Wkshts\Test0607Returning.xls")
End If

Dim wkbkG As Workbook

Set wkbkG = Nothing
On Error Resume Next
Set wkbkG = Workbooks("c07ret_07a.xls")
On Error GoTo 0

If wkbkG Is Nothing Then
'it's not open
Set wkbkG =
Workbooks.Open(Filename:="G:\FinPlan\Reports\Automated
Awd Wkshts\c07ret_07a.xls")
End If
Set FormWks = wkbkG.Worksheets("c07ret_07a")

myAddresses = Array("j4", "d4", "d10", "d11", "d7", "i16", "i19",
"i21", "i27")

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




If the workbook isn't open, then excel can't find the stuff it needs.

Since you could have lots of different workbooks that are named
testform.xls (in
lots of different folders, in different shared drives, on different
CDs,
on
different USB devices, ....) excel wouldn't know which one to open.

If you want to have excel open your workbooks, you can do:

Set FormWks _
= workbooks.open(filename:="C:\my documents\excel\testform.xls") _
.Worksheets("sheet1")

But then this expects the workbook to be closed!

You could have the code check.

dim wkbkF as workbook

set wkbkF = nothing
on error resume next
set wkbkf = workbooks("TestForm.xls")
on error goto 0

if wkbkf is nothing then
'it's not open
set wkbkf = workbooks.open(filename:="C:\my
documents\excel\testform.xls")
end if

set formwks = wkbkf.worksheets("Sheet1")



Bubba Gump wrote:

well "no rhyme or reason" is inaccurate in that the data is arranged
with
data similar in nature. However, it's not just sitting in straight
columns/rows and there are merged cells all over hell and creation
depending
on the size of the element that is in that position. does that make
sense?

I'm gonna play with this this afternoon and see what I get. you say
the
workbooks have to already be open? why is that? will it not open them
if
they're in the same folder? In other words, if i open the form
workbook
and
run the macro, will it not open the data file if it resides in the
same
folder as the form file? if not, is there a macro comman that will
open
it?

Thanks dave!

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

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

So use
set formwks =
worksheets("whatevertheworksheetnameisthatholdstheformhere")
and
set datawks =
worksheets("whatevertheworksheetnameisthatholdsthedatahere")

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).

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
Click to expand...
Click to expand...
Click to expand...
 
If you didn't get any print preview screens, then something bad happened.

Remember that it looks at column A to find an indicator to print the data on
that row. If you have anything in that cell, then that row is used to populate
the form--if all of column A is empty, then you're not asking for anything to be
populated.

Bubba said:
ok, to answer your question, yes it is always the first sheet but it looks
like you went ahead and included that in this latest version of the code. I
ran the macro and got no errors, but at the same time, it didn't seem to do
anything. I have the form file open, did alt-f8 and selected the testme
macro and ran it. It opened up the data file and then i couldn't tell that
anything else happened. Am I missing something?

Thanks!
Buster

Dave Peterson said:
When you do this stuff, is there only one worksheet in each of those
workbooks?

If there's only one worksheet, you can just use the first (and only)
worksheet
in the workbook and avoid having to use the names.

Option Explicit
Option Base 0
Sub testme()
Dim WkbkF As Workbook
Dim WkbkD As Workbook
Dim FormWks As Worksheet
Dim DataWks As Worksheet
Dim WkbkFName As String
Dim WkbkDName As String

Dim myRng As Range
Dim myCell As Range
Dim iCtr As Long
Dim myAddresses As Variant

WkbkFName = "Test0607Returning.xls"
Set WkbkF = Nothing
On Error Resume Next
Set WkbkF = Workbooks(WkbkFName)
On Error GoTo 0

If WkbkF Is Nothing Then
'it's not open
Set WkbkF = Workbooks.Open _
(Filename:="G:\FinPlan\Reports\Automated Awd
Wkshts\" _
& WkbkFName)
End If
Set FormWks = WkbkF.Worksheets(1)

WkbkDName = "c07ret_07a.xls"

Set WkbkD = Nothing
On Error Resume Next
Set WkbkD = Workbooks(WkbkDName)
On Error GoTo 0

If WkbkD Is Nothing Then
'it's not open
Set WkbkD = Workbooks.Open _
(Filename:="G:\FinPlan\Reports\Automated Awd
Wkshts\" _
& WkbkDName)
End If
Set DataWks = WkbkD.Worksheets(1)

myAddresses = Array("j4", "d4", "d10", "d11", "d7", "i16", _
"i19", "i21", "i27")

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

I changed the wkbkF and wkbkG names to wkbkF and wkbkD (F=Form, D=data) to
make
it easier to know which represents which. Make sure I did the change
correctly!

And if I can always use the first workbook (left most when you're looking
at it
in excel), you can use:
Set FormWks = WkbkF.Worksheets(1)
instead of specifying the name.

When you get that error message:
error '91': Object variable or With block variable not set.
on this line:
with DataWks

It's because DataWks hasn't be initialized correctly. It could be because
of a
few things that may have been fixed (now) (worksheets(1) may have fixed
it).

And just think how dangerous you're becoming learning all this stuff.

Remember to save often!

(and get rid of that Preview:=true when you're ready for paper.)



Bubba said:
I found one problem with this in looking back at it but it didn't fix the
problem. After the endif following "Set wkbkF", i forgot to include the
line
"Set FormWks = wkbkF.Worksheets("Sheet1")".

In case you notice on the line of mine that has the worksheet name as
c07ret_07a, FoxPro names the sheet the same thing as the file when it
exports the file to Excel.

ok, this is what I have and it bombs on the set myRng line. "Run-time
error '91': Object variable or With block variable not set." Man you're
gonna wish this thread had never surfaced.

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
Dim wkbkF As Workbook

Set wkbkF = Nothing
On Error Resume Next
Set wkbkF = Workbooks("Test0607Returning.xls")
On Error GoTo 0

If wkbkF Is Nothing Then
'it's not open
Set wkbkF =
Workbooks.Open(Filename:="G:\FinPlan\Reports\Automated
Awd Wkshts\Test0607Returning.xls")
End If

Dim wkbkG As Workbook

Set wkbkG = Nothing
On Error Resume Next
Set wkbkG = Workbooks("c07ret_07a.xls")
On Error GoTo 0

If wkbkG Is Nothing Then
'it's not open
Set wkbkG =
Workbooks.Open(Filename:="G:\FinPlan\Reports\Automated
Awd Wkshts\c07ret_07a.xls")
End If
Set FormWks = wkbkG.Worksheets("c07ret_07a")

myAddresses = Array("j4", "d4", "d10", "d11", "d7", "i16", "i19",
"i21", "i27")

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




If the workbook isn't open, then excel can't find the stuff it needs.

Since you could have lots of different workbooks that are named
testform.xls (in
lots of different folders, in different shared drives, on different
CDs,
on
different USB devices, ....) excel wouldn't know which one to open.

If you want to have excel open your workbooks, you can do:

Set FormWks _
= workbooks.open(filename:="C:\my documents\excel\testform.xls") _
.Worksheets("sheet1")

But then this expects the workbook to be closed!

You could have the code check.

dim wkbkF as workbook

set wkbkF = nothing
on error resume next
set wkbkf = workbooks("TestForm.xls")
on error goto 0

if wkbkf is nothing then
'it's not open
set wkbkf = workbooks.open(filename:="C:\my
documents\excel\testform.xls")
end if

set formwks = wkbkf.worksheets("Sheet1")



Bubba Gump wrote:

well "no rhyme or reason" is inaccurate in that the data is arranged
with
data similar in nature. However, it's not just sitting in straight
columns/rows and there are merged cells all over hell and creation
depending
on the size of the element that is in that position. does that make
sense?

I'm gonna play with this this afternoon and see what I get. you say
the
workbooks have to already be open? why is that? will it not open them
if
they're in the same folder? In other words, if i open the form
workbook
and
run the macro, will it not open the data file if it resides in the
same
folder as the form file? if not, is there a macro comman that will
open
it?

Thanks dave!

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

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

So use
set formwks =
worksheets("whatevertheworksheetnameisthatholdstheformhere")
and
set datawks =
worksheets("whatevertheworksheetnameisthatholdsthedatahere")

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).

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
Click to expand...
Click to expand...
Click to expand...
 
Could you please tell me what is wrong with this MACRO???

Sub TESTCompare()
' TESTNEW Macro
' Macro recorded 8/7/2006 by gokulraj.prabakaran

Dim FormWks As Worksheet
Dim DataWks As Worksheet
Dim myRng As Range
Dim myCell As Range
Dim NumRows
Dim rn As Range
Dim CellValue
ActiveCell.CurrentRegion.Select
Set rn = Selection.Areas(1)
NumRows = rn.Rows.Count

For i = 1 To NumRows

Set FormWks = Worksheets("Source")
Set DataWks = Worksheets("Dest")

If IsEmpty(FormWks.Cells(i, 1).Value) = IsEmpty(DataWks.Cells(i,
1).Value) Then
DataWks.Cells(i, 7).Value = FormWks.Cells(i, 5).Value
DataWks.Cells(i, 8).Value = FormWks.Cells(i, 6).Value

End If

Next i

End Sub

I want the respective values of Row 2 of Sheet#1 to Row 1 of Sheet#2

but the way Macro works is Row 1 of Shee#1 to Row 1 of Sheet#2 :-(((

------------------------------------------------------------------------------------------------
WORKSHEET #1

TEST1 TEST2 TEST3 TEST4 TEST5 TEST6 TEST7
value1 abc 122 34GH Hai TENDER
value2 abc 122 33DE JAI DONE
value3 abc 122 35TH FLY PROGRESS
value4 21ST FLY
value5 def 923F FLY
value6 def 122 232I FLY PENDING


THIS is WORKSHEET #2

TEST1 TEST2 TEST3 TEST4 TEST6 TEST7
value2 abc 122 33DE Hai TENDER
value4 21ST JAI DONE
value5 def 923F FLY PROGRESS
value6 def 122 232I FLY
------------------------------------------------------------------------------------------------
 
Back
Top