How to Transfer Data From One Worksheet to Another Automatically in Excel Using VBA Macro
Download the featured file: https://www.bluepecantraining.com/wp-content/uploads/2021/05/Transfer-data-from-one-Excel-worksheet-to-another-with-macro-demo.xlsx
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.
Please note I am unable to upload macro-enabled files to WordPress, so I have included a copy of the VBA code below:
'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
Видео How to Transfer Data From One Worksheet to Another Automatically in Excel Using VBA Macro канала Chester Tugwell
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.
Please note I am unable to upload macro-enabled files to WordPress, so I have included a copy of the VBA code below:
'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
Видео How to Transfer Data From One Worksheet to Another Automatically in Excel Using VBA Macro канала Chester Tugwell
Показать
Комментарии отсутствуют
Информация о видео
Другие видео канала
![Excel VBA: Copy Data Between ANY Two Sheets](https://i.ytimg.com/vi/azWysZ6qZWQ/default.jpg)
![How to Compare Two Excel Sheets and Find Differences](https://i.ytimg.com/vi/nrDOQ_VDmQA/default.jpg)
![](https://i.ytimg.com/vi/DfO5LZ0JXJI/default.jpg)
![Excel Automatically Sort When Data Changes or Added | Auto Sort Excel Formula | Auto Sort Macro](https://i.ytimg.com/vi/J4a25byznWQ/default.jpg)
![Due Date Pop-up Alert in Excel | Date Due Tracker - Formula & Due Date Color Change Notifications](https://i.ytimg.com/vi/eLBGhAvgCkc/default.jpg)
![Excel Date Format Won't Change | I Can't Change Excel Date Format!](https://i.ytimg.com/vi/Ya10z2gfRB8/default.jpg)
![Life Lessons From 100-Year-Olds](https://i.ytimg.com/vi/9AThycGCakk/default.jpg)
![VBA to BROWSE & COPY Data from SELECTED File in Excel](https://i.ytimg.com/vi/h_sC6Uwtwxk/default.jpg)
![How to Put Excel Formulas in a Word Document](https://i.ytimg.com/vi/oM--iZLFe1E/default.jpg)
![Excel - Find Numbers Between Range | FILTER & VLOOKUP to Find a Value that Falls Between a Range](https://i.ytimg.com/vi/UcDMke1kzhU/default.jpg)
![Excel VBA: Copy Data from one Sheet to Another (Absolute Vs. Relative Macro Recording)](https://i.ytimg.com/vi/8IreWUk1Al4/default.jpg)
![VBA Macro to Copy Data from Another Workbook in Excel](https://i.ytimg.com/vi/9R0_h_aoSAE/default.jpg)
![Can you Filter Horizontally in Excel? | Can You Filter Rows Instead of Columns in Excel? | 3 Methods](https://i.ytimg.com/vi/rIm7dYPvpL0/default.jpg)
![Copy & Paste in Excel VBA (copy, pastespecial, resize & offset)](https://i.ytimg.com/vi/p4ViTxIl0E0/default.jpg)
![How to do an IF VLOOKUP | IF and VLOOKUP Nested Function | IF TRUE Return Value From Another Column](https://i.ytimg.com/vi/I1CkFSqicbM/default.jpg)
![Why is Excel Selecting Multiple Cells When I Click on One? | Excel is Selecting Too Many Cells](https://i.ytimg.com/vi/uhK9AXT5z_I/default.jpg)
![Can Excel Add Up Times? | Including Adding Time in Excel Over 24 hours](https://i.ytimg.com/vi/T5WQ7yunIlk/default.jpg)
![MS Excel - Import Live Data From Web to Excel](https://i.ytimg.com/vi/2yTAyVXzFGg/default.jpg)
![Excel VBA - Copy Paste Data Range From Another Worksheet or Workbook - Part 5](https://i.ytimg.com/vi/FIiTwmIpzi4/default.jpg)
![Transfer Data in Excel From One Worksheet to Another Automatically Using VBA and Macros](https://i.ytimg.com/vi/tcLNWvhaUH8/default.jpg)