Private Sub CommandButton2_Click()
CommandButton2_Click:

'Dim Variables
Dim i As Integer
Dim Product As Integer
Dim NextRow As Integer

'''''''''''''''''''''''''''''''
'If B1 = blank then...
If Sheets(1).Range("B1").Value = "" Then
'Bring up a MsgBox and request cell entry
MsgBox "Please enter a Member ID into highlighted cell B1", vbOKOnly, "Enter Member ID"
'End the Sub
Exit Sub
'''''''''''''''''''''''''''''''

Else

'If A14 : A13 = blank then...
If Sheets(1).Range("A4").Value = "" And Sheets(1).Range("A5").Value = "" And Sheets(1).Range("A6").Value = "" And Sheets(1).Range("A7").Value = "" And Sheets(1).Range("A8").Value = "" And Sheets(1).Range("A9").Value = "" And Sheets(1).Range("A10").Value = "" And Sheets(1).Range("A11").Value = "" And Sheets(1).Range("A12").Value = "" And Sheets(1).Range("A13").Value = "" Then
'Bring up a MsgBox requesting a product to be selected
MsgBox "Please select a product to purchase in Sheet1", vbOKOnly, "Select a Product"
'End the Sub if true
Exit Sub

Else
'''''''''''''''''''''''''''''''

'''''''''''''''''''''''''''''''
' *** INSERT INTO SHEET 3 *** '
'''''''''''''''''''''''''''''''
'Set Variables
Product = 4
'Set up Loop
Do While Product <= 13
'Set Variables
NextRow = LastCell(Sheet3).Row
i = NextRow
MsgBox i

'ACTUAL CODING TO INSERT PRODUCT
If Sheets(1).Range("A" & Product).Value <> "" Then
Sheets(3).Range("A" & i).Value = i - 1
Sheets(3).Range("B" & i).Value = Sheets(1).Range("B1").Value
End If
'END OF INSERTION

'Set Variables before looping
Product = Product + 1
'Loop
Loop


End If
End If
End Sub

Function LastCell(ws As Worksheet) As Range
  Dim LastRow&, LastCol%

' Error-handling is here in case there is not any
' data in the worksheet

  On Error Resume Next

  With ws

  ' Find the last real row

    LastRow& = .Cells.Find(What:="*", _
      SearchDirection:=xlPrevious, _
      SearchOrder:=xlByRows).Row

  ' Find the last real column

    LastCol% = .Cells.Find(What:="*", _
      SearchDirection:=xlPrevious, _
      SearchOrder:=xlByColumns).Column

  End With

' Finally, initialize a Range object variable for
' the last populated row.

  Set LastCell = ws.Cells(LastRow&, LastCol%)

End Function
I currently have this code, but it isn't working as I desire.

I have 4 sheets, but this part of my VBA is only concerning two of them: Sheet1 and Sheet3.

In Sheet1 I have a table where the user can enter up to 10 products to purchase goods. The user enters a "MemberID" into cell "B1". The user then enters up to 10 product names from cell "A4" up to cell "A13". I want the user to press a command button and the data will be sent to Sheet3....

Sheet3 is essentially a logging table, where a log of all the products are kept. If a product is in cell "A4" in sheet1 then it will be put into the next available cell in Sheet3 in column "C". If there isn't one then it will check cell "A4" and put it into Sheet3 and so on... However, I am not concerned about that yet, if this first problem is fixed then that can be fixed easily too. In column "A" in Sheet3 I have a PurchaseID, essentially a primary key. I want this command button to find the next blank cell in column "A" Sheet3, and then insert into it the next integer in the PurchaseID sequence. I have had a stab at it, but I cannot get it to work - what is going on? I can only seem to get it to put the PurchaseID for the first product (into row 2 Sheet3)and that is it.

If you still don't understand then post and say so.