Getting From UserForm TextBoxes To Lastrow On Active Sheet

[Minitman]

Greetings,

I am trying to populate the first 12 columns in the last row of the
active sheet (Food_List) from 12 UserForm TextBoxes (TB5 thru TB16).
And/or populate the first 6 columns in the last row of the active
sheet (Daily_Log) from 6 UserForm TextBoxes (TB1 thru TB6). It
depends on which CommandButton is clicked.

Code for 'Post To Daily Log' CommandButton:

Private Sub PostToDailyLogButton_Click()
Set WS2 = Worksheets("Daily_Log")
WS2.Activate
vLastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("A" & vLastRow & ":AE" & vLastRow).Copy _
Destination:=Range("A" & vLastRow + 1 & ":AE" & vLastRow + 1)
Application.CutCopyMode = False
With Range("A65536").End(xlUp)
For i = 1 To 6
.Offset(0, i - 1).Value = Me.Controls("TB" & i).Value
MsgBox "Cycle " & i
Next i
End With
Unload Me
End Sub

Code for 'Post To Food List' CommandButton:

Private Sub PostToFoodListButton_Click()
Set WS1 = Worksheets("Food_List")
WS1.Activate
vLastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("A" & vLastRow & ":L" & vLastRow).Copy _
Destination:=Range("A" & vLastRow + 1 & ":L" & vLastRow + 1)
Application.CutCopyMode = False
With Range("A65536").End(xlUp)
For i = 1 To 12
.Offset(0, i - 1).Value = Me.Controls("TB" & i + 4).Value
MsgBox "Cycle " & i
Next i
End With
Unload Me
End Sub

Problem: If I click on the Daily Log button, the message box comes up
once each cycle (eg. Cycle 1, Cycle 2, Cycle 3, Cycle 4, Cycle 5 &
Cycle 6) and the result is as anticipated. However, if I click on the
Food List button the message box comes up only once with Cycle 13
(which is not a legitimate response, Cycle 1 thru Cycle 12 are
legitimate responses). The code copies the last row of the active
sheet to the next row down (which is good) and then copies the first
of the 12 TextBoxes to the first of the 12 cells in the now last row
(over writing the contents but not the formats of that cell, which is
still good). But there it stops (which is NOT good).

Why does this code work on one sheet but not the other?

Anyone have any ideas as to why and how to fix it?

I have a sample workbook with the 2 sheets and the UserForm ready if
anyone is interested in seeing it.

Thanks for looking at my post and for any help you may be able and
willing to offer.

-Minitman

 

[stevebriz]

Email me your xls ..I'll take a look

 

[stevebriz]

I think found your problem
You have "i" declared on a module level ( in the declaration section)

When you select from the TB5 list box it leaves i with value of 12.
you can either:
Remove Dim i as integer from the declarations section and declare in
each
procedure.
or Change the food list procedure to use say j instead of i
and Dim j as an integer at the start of the procedure.

 

[Minitman]

Thanks Steve,

That was indeed the problem and both of your solutions fixed the
problem.

I'll have to watch my declarations in the future!

-Minitman