How do I modify the code below to stop running on each row with data until it
hits a blank row? I want to use a command button to execute for one row of
data only. This is a follow up to a post from Dave Petersen (he wrote the
code below) last week. Thanks all!

Private Sub Merge_Click()

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("a", "b", "c", "d", "e", "f", "g", "h", "i", "k", "l", "m",
"o", "p")
tAddr = Array("a1", "b1", "d12", "e13", _
"c5", "a18", "a15", "c12", "f3", "f4", _
"f17", "g9", "e1", "e2")

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

Set fWks = Worksheets("Entry")
Set tWks = Worksheets("Form")

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

Based on the location of the activecell?

If yes, you could cheat and just change this portion:

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

becomes

FirstRow = activecell.row
LastRow = activecell.row
'or even
'Lastrow = firstrow

tjb wrote:

How do I modify the code below to stop running on each row with data until it
hits a blank row? I want to use a command button to execute for one row of
data only. This is a follow up to a post from Dave Petersen (he wrote the
code below) last week. Thanks all!

Private Sub Merge_Click()

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("a", "b", "c", "d", "e", "f", "g", "h", "i", "k", "l", "m",
"o", "p")
tAddr = Array("a1", "b1", "d12", "e13", _
"c5", "a18", "a15", "c12", "f3", "f4", _
"f17", "g9", "e1", "e2")

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

Set fWks = Worksheets("Entry")
Set tWks = Worksheets("Form")

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

--

Dave Peterson

Awesome, now how would you go about making it go to the next line each time
the button is clicked? Until it gets to an empty row?

"Dave Peterson" wrote:

Based on the location of the activecell?

If yes, you could cheat and just change this portion:

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

becomes

FirstRow = activecell.row
LastRow = activecell.row
'or even
'Lastrow = firstrow

tjb wrote:

How do I modify the code below to stop running on each row with data until it
hits a blank row? I want to use a command button to execute for one row of
data only. This is a follow up to a post from Dave Petersen (he wrote the
code below) last week. Thanks all!

Private Sub Merge_Click()

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("a", "b", "c", "d", "e", "f", "g", "h", "i", "k", "l", "m",
"o", "p")
tAddr = Array("a1", "b1", "d12", "e13", _
"c5", "a18", "a15", "c12", "f3", "f4", _
"f17", "g9", "e1", "e2")

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

Set fWks = Worksheets("Entry")
Set tWks = Worksheets("Form")

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

--

Dave Peterson

At the bottom...

Activecell.offset(1,0).select



tjb wrote:

Awesome, now how would you go about making it go to the next line each time
the button is clicked? Until it gets to an empty row?

"Dave Peterson" wrote:

Based on the location of the activecell?

If yes, you could cheat and just change this portion:

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

becomes

FirstRow = activecell.row
LastRow = activecell.row
'or even
'Lastrow = firstrow

tjb wrote:

How do I modify the code below to stop running on each row with data until it
hits a blank row? I want to use a command button to execute for one row of
data only. This is a follow up to a post from Dave Petersen (he wrote the
code below) last week. Thanks all!

Private Sub Merge_Click()

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("a", "b", "c", "d", "e", "f", "g", "h", "i", "k", "l", "m",
"o", "p")
tAddr = Array("a1", "b1", "d12", "e13", _
"c5", "a18", "a15", "c12", "f3", "f4", _
"f17", "g9", "e1", "e2")

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

Set fWks = Worksheets("Entry")
Set tWks = Worksheets("Form")

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

--

Dave Peterson


--

Dave Peterson