Download the featured file and code: https://www.bluepecantraining.com/wp-...

In this video I demonstrate how to automatically transfer data from one worksheet to another using a VBA macro. In the example used in this video, a call centre operative records a call's detail of a call on a form in one worksheet and then is able to click a button to transfer the details over to a call log on another worksheet.

Here's the VBA code

'Create and set variables for the Call Tracking & Call Log worksheets
Dim CTrk As Worksheet, CLog As Worksheet

Set CTrk = Sheet1
Set CLog = Sheet2

'Create and set variables for each cell in the call tracking sheet
Dim NoCalled As Range, CustName As Range, CallNature As Range, CallOutCome As Range
Dim CallDur As Range, CallDate As Range, CallFUp As Range

Set NoCalled = CTrk.Range("D8")
Set CustName = CTrk.Range("G8")
Set CallNature = CTrk.Range("D11")
Set CallOutCome = CTrk.Range("G11")
Set CallDur = CTrk.Range("J8")
Set CallDate = CTrk.Range("J11")
Set CallFUp = CTrk.Range("G14")

Create a variable for the paste cell in the Call Log worksheet
Dim DestCell As Range

If CLog.Range("A2") = "" Then 'If A2 is empty
Set DestCell = CLog.Range("A2") '...then destination cell is A2
Else
Set DestCell = CLog.Range("A1").End(xlDown).Offset(1, 0) '...otherwise the next empty row
End If

'If no "Number called has been entered, exit macro
If NoCalled = "" Then
MsgBox "You must enter a Number called before adding to the log"
Exit Sub
End If

'Copy and paste data from the Call Tracking worksheet to the Call Log worksheet
NoCalled.Copy DestCell
CallDur.Copy DestCell.Offset(0, 1)
CallDate.Copy DestCell.Offset(0, 2)
CustName.Copy DestCell.Offset(0, 3)
CallNature.Copy DestCell.Offset(0, 4)
CallOutCome.Copy DestCell.Offset(0, 5)
CallFUp.Copy DestCell.Offset(0, 6)

'Clear the contents in the Call Tracking worksheet
NoCalled.ClearContents
CustName.ClearContents
CallNature.ClearContents
CallOutCome.ClearContents
CallDur.ClearContents
CallDate.ClearContents
CallFUp.ClearContents