I am trying to make this code auto save data to the next row but it wont work any help appreciated.
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(78, 4).Value
.Offset(0, 4).Value = wsForm.Cells(78, 6).Value
.Offset(0, 5).Value = wsForm.Cells(78, 8).Value
.Offset(0, 6).Value = wsForm.Cells(78, 10).Value
.Offset(0, 7).Value = wsForm.Cells(78, 12).Value
.Offset(0, 8).Value = wsForm.Cells(78, 14).Value
End With
'confirm data transferred
MsgBox "Data transferred successfully" & vbCrLf & "", vbInformation, "Data transfer"
End Sub
Bookmarks