Referencing another Worksheet in VBA

[scavazo4]

I was given this project to develop a form that by a click a button will send the answers to a separate table. It currently works. The user fills out the form in "Change Order Form (sheet1)" , when finished the submit button is clicked which then sends the responses a table in "Change Order Log (sheet2)". What I need to do now, is change the location of "

Change Order Log (sheet2)​

" to another worksheet. They want me to have the form "Change Order Form (sheet1)" as a separate standard document. Every time a user downloads it and fills out the form, the responses are sent to the same "

Change Order Log (sheet2)​

" but in another worksheet that is probably stored in a sharepoint. The more I read it, it sounds like it can just be solved by creating a google survey but they are pretty strict of sticking to this method. Any help would be greatly appreciated!
Here is the current functioning code.

Private Sub CommandButton1_Click()
Dim JobNumber As Double, WorkOrder As Double, EnteredBy As String, CurrentDate As String, Requestor As String, Acceptor As String
Dim SubPSL As String, Customer As String, TypeofChange As String, ReasonForChange As String, OvertimeRequired As String
Dim TotalTime As Integer, NumberOfHourlyPersonnel As Integer, BurdenRate As Integer, Parts As String, PartsVendor As String
Dim PartsCost As Double, AdditionalCost As Double, ResponsibleForCost As String, TotalCost As Double, Notes As String
Worksheets("ChangeOrderForm").Select
JobNumber = Range("B10")
WorkOrder = Range("B11")
EnteredBy = Range("B12")
CurrentDate = Range("B13")
Requestor = Range("B14")
Acceptor = Range("B15")
SubPSL = Range("B16")
Customer = Range("B17")
TypeofChange = Range("B18")
ReasonForChange = Range("B19")
OvertimeRequired = Range("B20")
TotalTime = Range("B21")
NumberOfHourlyPersonnel = Range("B22")
BurdenRate = Range("B23")
Parts = Range("B24")
PartsVendor = Range("B25")
PartsCost = Range("B26")
AdditionalCost = Range("B27")
ResponsibleForCost = Range("B28")
Notes = Range("B29")
TotalCost = Range("B30")

Worksheets("Change Order Log").Select
Worksheets("Change Order Log").Range("A1").Select
If Worksheets("Change Order Log").Range("A1").Offset(1, 0) <> "" Then
Worksheets("Change Order Log").Range("A1").End(xlDown).Select
End If
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = JobNumber
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = WorkOrder
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = EnteredBy
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = CurrentDate
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Requestor
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Acceptor
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = SubPSL
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Customer
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = TypeofChange
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = ReasonForChange
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = OvertimeRequired
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = TotalTime
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = NumberOfHourlyPersonnel
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = BurdenRate
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Parts
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = PartsVendor
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = PartsCost
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = AdditionalCost
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = ResponsibleForCost
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = TotalCost
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Notes
ActiveCell.Offset(0, 1).Select
Worksheets("ChangeOrderForm").Select
Worksheets("ChangeOrderForm").Range("B10:B29").ClearContents
End Sub
Private Sub CommandButton1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
End Sub

 

[dmt32]

Alright so it seems like the responses are being stored in the heading of each column in the table. Thus making the submissions begin in row 2 would solve this issue. Also is there a way for cell B30 to stay intact after each submission. That is B10:B29 clears after every submission, but the format in B30 stays the same? B30 contains a formula which gathers the summation of several cells above.

If its a TABLE you are sending data to then some changes to my original code may help

Place following code in a STANDARD module

Function NewRow(ByVal sh As Object) As Range
    On Error Resume Next
    Nextrow = sh.Cells.Find(What:="*", _
                            After:=sh.Range("A1"), _
                            Lookat:=xlPart, LookIn:=xlFormulas, _
                            SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _
                            MatchCase:=False).Row
    Set NewRow = sh.Cells(Nextrow + 1, 1)
    On Error GoTo 0
End Function

In the CommnadButton code REPLACE this line

Set NextRecord = wbDatabase.Worksheets(1).Range("A1").End(xlDown).Offset(1, 0)

with THIS line

Set NextRecord = NewRow(wbDatabase.Worksheets(1))

Also, to stop cells that contain formulas being cleared REPLACE this line

DataEntryRange.ClearContents

with THIS line

DataEntryRange.Cells.SpecialCells(xlCellTypeConstants).ClearContents

and see if these changes resolve the issues

Dave