making excel work like a mail merge
 

I have a form that I've created in Excel (because Word forms are too unruly
and difficult to manage) but I need to be able to merge data from another
sheet that's in a table format into the form.

I'm envisioning something like a command button that will enter in single
rows of data into specified cells in the form each time the button is clicked.

So for example, the user clicks CommandButton1 which then looks at Sheet2
and enters data from A1:A15 into various cells on Sheet1, prints Sheet1 and
then moves on to look at B1:B15 from Sheet2 until it gets to a blank row and
then stops.

I know some of you masters out there can help with this! Thanks all!

making excel work like a mail merge
 

I think you got your rows and columns mixed up (A1:A15 is not in a single row).

But if you lay out your data in rows (going across), you could use column A as
an indicator. If column A is empty, then skip that row. If it's got something
in it, then process it.

Option Explicit
Sub testme()

Dim fWks As Worksheet
Dim tWks As Worksheet

Dim fCol As Variant
Dim tAddr As Variant

Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim iCtr As Long

fCol = Array("b", "c", "e") 'd skipped as an example
tAddr = Array("b12", "c19", "x45")

If UBound(fCol) < UBound(tAddr) Then
MsgBox "design error--not same number of columns/cells)"
End If

Set fWks = Worksheets("Input")
Set tWks = Worksheets("Master")

With fWks
FirstRow = 2
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = FirstRow To LastRow
If IsEmpty(.Cells(iRow, "A")) Then
'skip this row
Else
For iCtr = LBound(fCol) To UBound(fCol)
tWks.Range(tAddr(iCtr)).Value _
= .Cells(iRow, fCol(iCtr)).Value
Next iCtr
tWks.PrintOut preview:=True
End If
Next iRow
End With

End Sub

You can modify these two lines:

fCol = Array("b", "c", "e") 'd skipped as an example
tAddr = Array("b12", "c19", "x45")

To map the column to the address.

And I used these two lines to specify the worksheet names.

Set fWks = Worksheets("Input")
Set tWks = Worksheets("Master")


tjb wrote:

I have a form that I've created in Excel (because Word forms are too unruly
and difficult to manage) but I need to be able to merge data from another
sheet that's in a table format into the form.

I'm envisioning something like a command button that will enter in single
rows of data into specified cells in the form each time the button is clicked.

So for example, the user clicks CommandButton1 which then looks at Sheet2
and enters data from A1:A15 into various cells on Sheet1, prints Sheet1 and
then moves on to look at B1:B15 from Sheet2 until it gets to a blank row and
then stops.

I know some of you masters out there can help with this! Thanks all!

--

Dave Peterson

making excel work like a mail merge
 

I did get my rows and columns mixed up, I meant to say something like A1:J1
and not the other way around.

This does just what I asked for. Thanks Dave!


"Dave Peterson" wrote:

I think you got your rows and columns mixed up (A1:A15 is not in a single row).

But if you lay out your data in rows (going across), you could use column A as
an indicator. If column A is empty, then skip that row. If it's got something
in it, then process it.

Option Explicit
Sub testme()

Dim fWks As Worksheet
Dim tWks As Worksheet

Dim fCol As Variant
Dim tAddr As Variant

Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim iCtr As Long

fCol = Array("b", "c", "e") 'd skipped as an example
tAddr = Array("b12", "c19", "x45")

If UBound(fCol) < UBound(tAddr) Then
MsgBox "design error--not same number of columns/cells)"
End If

Set fWks = Worksheets("Input")
Set tWks = Worksheets("Master")

With fWks
FirstRow = 2
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = FirstRow To LastRow
If IsEmpty(.Cells(iRow, "A")) Then
'skip this row
Else
For iCtr = LBound(fCol) To UBound(fCol)
tWks.Range(tAddr(iCtr)).Value _
= .Cells(iRow, fCol(iCtr)).Value
Next iCtr
tWks.PrintOut preview:=True
End If
Next iRow
End With

End Sub

You can modify these two lines:

fCol = Array("b", "c", "e") 'd skipped as an example
tAddr = Array("b12", "c19", "x45")

To map the column to the address.

And I used these two lines to specify the worksheet names.

Set fWks = Worksheets("Input")
Set tWks = Worksheets("Master")


tjb wrote:

I have a form that I've created in Excel (because Word forms are too unruly
and difficult to manage) but I need to be able to merge data from another
sheet that's in a table format into the form.

I'm envisioning something like a command button that will enter in single
rows of data into specified cells in the form each time the button is clicked.

So for example, the user clicks CommandButton1 which then looks at Sheet2
and enters data from A1:A15 into various cells on Sheet1, prints Sheet1 and
then moves on to look at B1:B15 from Sheet2 until it gets to a blank row and
then stops.

I know some of you masters out there can help with this! Thanks all!

--

Dave Peterson