Save data to one row then next time to next row.

[Justin25150]

Hi All,

The problem I am having is that I have created a save to database button, now it will save to another sheet the first time correctly but when push it again it wont automatically save to the next row, details are as below.

Option Explicit
Dim wsData As Worksheet
Dim wsForm As Worksheet
Dim rNextCl As Range
Sub Save_Data1()

'Set variables
Set wsData = Sheet5
Set wsForm = Sheet1
'find the next empty row for data input
Set rNextCl = wsData.Cells(65536, 1).End(xlUp).Offset(1, 0)

'enter the data using Offset to find next Column
'note use With....End With to let Excel that the following lines are working with rNextCl
'also reduces typing!!

With rNextCl
.Value = wsForm.Cells(2, 2).Value
.Offset(0, 2).Value = wsForm.Cells(9, 4).Value
.Offset(0, 3).Value = wsForm.Cells(76, 4).Value
.Offset(0, 4).Value = wsForm.Cells(76, 6).Value
.Offset(0, 5).Value = wsForm.Cells(76, 8).Value
.Offset(0, 6).Value = wsForm.Cells(76, 10).Value
.Offset(0, 7).Value = wsForm.Cells(76, 12).Value
.Offset(0, 8).Value = wsForm.Cells(76, 14).Value
.Offset(0, 10).Value = wsForm.Cells(9, 4).Value
.Offset(0, 11).Value = wsForm.Cells(77, 4).Value
.Offset(0, 12).Value = wsForm.Cells(77, 6).Value
.Offset(0, 13).Value = wsForm.Cells(77, 8).Value
.Offset(0, 14).Value = wsForm.Cells(77, 10).Value
.Offset(0, 15).Value = wsForm.Cells(77, 12).Value
.Offset(0, 16).Value = wsForm.Cells(77, 14).Value
.Offset(0, 18).Value = wsForm.Cells(9, 4).Value
.Offset(0, 19).Value = wsForm.Cells(78, 4).Value
.Offset(0, 20).Value = wsForm.Cells(78, 6).Value
.Offset(0, 21).Value = wsForm.Cells(78, 8).Value
.Offset(0, 22).Value = wsForm.Cells(78, 10).Value
.Offset(0, 23).Value = wsForm.Cells(78, 12).Value
.Offset(0, 24).Value = wsForm.Cells(78, 14).Value
.Offset(0, 26).Value = wsForm.Cells(9, 4).Value
.Offset(0, 27).Value = wsForm.Cells(79, 4).Value
.Offset(0, 28).Value = wsForm.Cells(79, 6).Value
.Offset(0, 29).Value = wsForm.Cells(79, 8).Value
.Offset(0, 30).Value = wsForm.Cells(79, 10).Value
.Offset(0, 31).Value = wsForm.Cells(79, 12).Value
.Offset(0, 32).Value = wsForm.Cells(79, 14).Value
.Offset(0, 34).Value = wsForm.Cells(9, 4).Value
.Offset(0, 35).Value = wsForm.Cells(80, 4).Value
.Offset(0, 36).Value = wsForm.Cells(80, 6).Value
.Offset(0, 37).Value = wsForm.Cells(80, 8).Value
.Offset(0, 38).Value = wsForm.Cells(80, 10).Value
.Offset(0, 39).Value = wsForm.Cells(80, 12).Value
.Offset(0, 40).Value = wsForm.Cells(80, 14).Value
.Offset(0, 42).Value = wsForm.Cells(9, 4).Value
.Offset(0, 43).Value = wsForm.Cells(81, 4).Value
.Offset(0, 44).Value = wsForm.Cells(81, 6).Value
.Offset(0, 45).Value = wsForm.Cells(81, 8).Value
.Offset(0, 46).Value = wsForm.Cells(81, 10).Value
.Offset(0, 47).Value = wsForm.Cells(81, 12).Value
.Offset(0, 48).Value = wsForm.Cells(81, 14).Value
.Offset(0, 50).Value = wsForm.Cells(9, 4).Value
.Offset(0, 51).Value = wsForm.Cells(82, 4).Value
.Offset(0, 52).Value = wsForm.Cells(82, 6).Value
.Offset(0, 53).Value = wsForm.Cells(82, 8).Value
.Offset(0, 54).Value = wsForm.Cells(82, 10).Value
.Offset(0, 55).Value = wsForm.Cells(82, 12).Value
.Offset(0, 56).Value = wsForm.Cells(82, 14).Value
.Offset(0, 58).Value = wsForm.Cells(9, 4).Value
.Offset(0, 59).Value = wsForm.Cells(83, 4).Value
.Offset(0, 60).Value = wsForm.Cells(83, 6).Value
.Offset(0, 61).Value = wsForm.Cells(83, 8).Value
.Offset(0, 62).Value = wsForm.Cells(83, 10).Value
.Offset(0, 63).Value = wsForm.Cells(83, 12).Value
.Offset(0, 64).Value = wsForm.Cells(83, 14).Value
.Offset(0, 66).Value = wsForm.Cells(9, 4).Value
.Offset(0, 67).Value = wsForm.Cells(84, 4).Value
.Offset(0, 68).Value = wsForm.Cells(84, 6).Value
.Offset(0, 69).Value = wsForm.Cells(84, 8).Value
.Offset(0, 70).Value = wsForm.Cells(84, 10).Value
.Offset(0, 71).Value = wsForm.Cells(84, 12).Value
.Offset(0, 72).Value = wsForm.Cells(84, 14).Value
End With

'confirm data transferred
MsgBox "Data transferred successfully" & vbCrLf & "", vbInformation, "Data transfer"


End Sub

[venkat1926]

it is not clear

however when you have already a copy statement
try this

Please Login or Register  to view this content.

now what does it do?

in the relevant sheet
it goes to the last cell lin A column
and goes up to mee the last row of the data
and psste the data in the cell next to the last row of the data. from A column