I've seen several threads here for this function but haven't been able to successfully modify those codes to work in my situation.
I have a form I created on Spreadsheet1 I named M635Frm used to enter data that will stored in a database format on SpreadSheet2 (Named M635Db). I viewd a couple of podcasts that showed how this is done and for the most part the code does fine, but my problem is copying the data to a row on the data entry form so I can move it to the next available row on the database sheet. I originally use a formula in the cells (such as =D2) in the destination cell) which worked until I poasted the data to Spreadsheet2 and the formulas were erased. Now I'm trying some different solutions I've found in the forums but have had no success so far. The current macro I have moves the entire cell and all of its formats:
Sub SetValue_LastRowOffset1()
If Range("D2").Value = "" Then
Range("D2").Value = Range("A200").Value
Else
Range("D" & ActiveSheet.Rows.Count).End(xlUp) _
.Offset(1, 0).Value = Range("A200").Value
End If
End Sub
and the Post Data macro is as follows:
Sub MoveRecord()
Dim WSF As Worksheet ' Form worksheet
Dim WSD As Worksheet ' Data worksheet
Set WSF = Worksheets("M635Frm")
Set WSD = Worksheets("M635Db")
NextRow = WSD.Cells(Rows.Count, 1).End(xlUp).Row + 1
' This next long line is where you copy each of the 20 fields to the database
WSD.Cells(NextRow, 1).Resize(1, 55).Value = Range("A200:BD200") 'Application.Transpose(.Value)
'WSD.Cells(NextRow, 1).Resize(1, 20).Value = Array( _
' Then you would have to clear the cells on the form
WSF.Range("A200:BD200").ClearContents
End Sub
I'm sure I have some errors here too I'm afraid. The form has 55 entries, some from a selection of radio butions and a few text boxes used for comments. The other entries are simple line data, Name, date, etc.. The object is to move the data entries down to a hidden row @ A200 and post the next available blank single row on sheet2. Thanks in advance for your assistance.
I have a form I created on Spreadsheet1 I named M635Frm used to enter data that will stored in a database format on SpreadSheet2 (Named M635Db). I viewd a couple of podcasts that showed how this is done and for the most part the code does fine, but my problem is copying the data to a row on the data entry form so I can move it to the next available row on the database sheet. I originally use a formula in the cells (such as =D2) in the destination cell) which worked until I poasted the data to Spreadsheet2 and the formulas were erased. Now I'm trying some different solutions I've found in the forums but have had no success so far. The current macro I have moves the entire cell and all of its formats:
Sub SetValue_LastRowOffset1()
If Range("D2").Value = "" Then
Range("D2").Value = Range("A200").Value
Else
Range("D" & ActiveSheet.Rows.Count).End(xlUp) _
.Offset(1, 0).Value = Range("A200").Value
End If
End Sub
and the Post Data macro is as follows:
Sub MoveRecord()
Dim WSF As Worksheet ' Form worksheet
Dim WSD As Worksheet ' Data worksheet
Set WSF = Worksheets("M635Frm")
Set WSD = Worksheets("M635Db")
NextRow = WSD.Cells(Rows.Count, 1).End(xlUp).Row + 1
' This next long line is where you copy each of the 20 fields to the database
WSD.Cells(NextRow, 1).Resize(1, 55).Value = Range("A200:BD200") 'Application.Transpose(.Value)
'WSD.Cells(NextRow, 1).Resize(1, 20).Value = Array( _
' Then you would have to clear the cells on the form
WSF.Range("A200:BD200").ClearContents
End Sub
I'm sure I have some errors here too I'm afraid. The form has 55 entries, some from a selection of radio butions and a few text boxes used for comments. The other entries are simple line data, Name, date, etc.. The object is to move the data entries down to a hidden row @ A200 and post the next available blank single row on sheet2. Thanks in advance for your assistance.
