Solved: Building Word Documents based on Individual Excel Rows

[IcePirates]

Hello,
Im seeking help ... I have a VB script that passes information from the first row of an excel document to a word document.

The first row has a "Create Document" button in the 6th cell ("F" column)

On every row there will be a "create document" button that will transfer
the information from that that row to a word document, but only the information in that row.

Im wondering what I would add to my script so that when the "Create Document" button is clicked for that row - the script only builds a word document based on the information in that specific row.
(Any help is appreciated!!)

Here is my code:
[vba]Option Explicit
Public Sub TransferData()
'This macro transfers the data range "A1:E1" to a table in Word
'
'Constants:
'docFullName = The full name of an already existing Word document
'
'Variables:
'doc = The Word document (assumed to be empty)
'i = A counter (for rows)
'j = A counter (for columns)
'tbl = A Word table
'wdRng = A Word range (the first paragraph of doc)
'wks = The worksheet "data" that contains the data range
'
'Const docFullName = "Automation\Word.doc" '
Dim doc As Object
Dim i As Long
Dim j As Long
Dim tbl As Object
Dim wdApp As Object 'Only if you require a new document each time
Dim wdRng As Object
Dim wks As Worksheet
'Assing Word objects 'Only if you require a new document each time
Set wdApp = CreateObject("Word.Application")
wdApp.Visible = True
Set doc = wdApp.Documents.Add
'Assign variables and objects
'Set doc = GetObject(docFullName) 'Only if you want a specific document
Set wdRng = doc.Paragraphs(1).Range
Set tbl = doc.Tables.Add(wdRng, 11, 5)
Set wks = ThisWorkbook.Worksheets("Transmittal")
'Transfer the data
With tbl
For i = 1 To 1
For j = 1 To 5
.Cell(i, j) = wks.Cells(i, j)
Next j
Next i
End With
'Save and close doc 'Only if you want a specific document
'Call doc.Save
'Call doc.Close(False)
'Clean
Set doc = Nothing
Set wks = Nothing
End Sub
Private Sub CommandButton2_Click()
End Sub[/vba]

Right now, this will build a document based on row 1 in the Excel document but
when someone add's information in Row 2, then what? I can add another form > button > but what do I add into my script so that when this button on row2 is clicked it only builds a word document based on the information in row 2

[IcePirates]

Would I just keep building Macro's until like line 50,000 and just keep assigning the next button to execute the next row and so on?

There has to be an easier way isnt there?

[Kenneth Hobs]

I would freeze a row or column and put the button there.
[VBA]For i = ActiveCell.Row To ActiveCell.Row[/VBA]

[RonMcK]

Kenneth,

Using his code would your suggestion change his code as follows?
[vba]
With tbl
For i = ActiveCell.Row To ActiveCell.Row ' <<youir change
For j = 1 To 5
.Cell(i, j) = wks.Cells(i, j)
Next j
Next i
End With
[/vba] How do you get the command button to appear on the line the user selects?

Thanks,

[JKwan]

Kenneth,

Using his code would your suggestion change his code as follows?
[vba]
With tbl
For i = ActiveCell.Row To ActiveCell.Row ' <<youir change
For j = 1 To 5
.Cell(i, j) = wks.Cells(i, j)
Next j
Next i
End With
[/vba] How do you get the command button to appear on the line the user selects?

Thanks,

How about this to get your button onto your active line?
[VBA]
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Application.Intersect(Range("C:C"), Target) Is Nothing Then
Sheet1.CommandButton1.Top = Target.Top
End If
End Sub

[/VBA]

[Kenneth Hobs]

Ron, I would not put the command button on the user's row. I would put it in the frozen row or frozen column. They would click the row they want to export and then click the button. One trick I used once was to make the button long and thin and put it in the first column and froze that column. This makes it very easy to use.

If one really needs the button to move, I would do something a bit like what JKwan did. e.g.
[vba]Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'If Target.Row = Me.CommandButton1.TopLeftCell.Row Then Exit Sub
Me.CommandButton1.Top = Range("F" & Target.Row).Top
Me.CommandButton1.Left = Range("F" & Target.Row).Left

End Sub[/vba]

Crossposted: http://www.mrexcel.com/forum/showthread.php?t=357019