How to Transfer Data From One Worksheet to Another Automatically in Excel Using VBA Macro скачать в хорошем качестве

How to Transfer Data From One Worksheet to Another Automatically in Excel Using VBA Macro

👍👍If you have found this content useful and want to show your appreciation, please use this link to buy me a beer 🍺. https://www.paypal.com/donate/?hosted... Thank you! 👍👍 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