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

Download the featured file and code: 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

[@silviopoggi8193]

Thank you so much for taking the time to produce and upload these excellent tutorials... I really could have used your services back in the day

[@IvanCortinas_ES]

Thank you for the macro Chester. You do a great job!!!

[@SuperHanme]

Thank you for the macro Chester

[@harilibo]

thanks for sharing the code, I found it really useful since I am trying to get more experience, coding in vba.

[@aka7minimouse]

Awesome video, thank you! Does anyone know how to do exactly this, but copy over only the values generated from a formula? Thanks!

[@johandaugaarddrejer9009]

Thank you a lot, very, very useful! :-)

[@juddyuk]

Hi Chester, very useful video thank you. Is there a way to send data to specific sheets depending on what customer is selected on the master sheet for example?

[@ramkrishnarao1212]

Super, it is working

[@seanain1]

Thank You Sir

[@jasonshirtcliffe9057]

Hi Chester really great video. I am trying to pull across data that are formula and have formats. I just want these to be values only tried to search but struggling

[@simaserslavas2897]

Perfect tutorial! Thank you!
Set NoCalled = CTrk.Range("D8") - is it possible set a longer range? I mean if I need that the information would be transfered not just from the D8 but from D8:D10 ?

[@sebjanavlashi5524]

Very good tutorial.
Is it possible to do the contrary? Suppose I have a table with data and I want to automatically transfer those data into the form fields?

[@greenmotiv4544]

This is very helpful to me. I just want to ask if my NoCalled cell is a cell that has a vlookup formula, how do I copy/paste the values only my Log without including the formula? Looking forward to your response. Thank you.

[@reginaldpetalcorin2049]

Thank you good sire. I just have one question. Is there a way to retain the score? I have a formula included In my main worksheet and when I click on the submit macro, the scores are messed up

[@keanujeeves-kinsella7516]

This is brilliant and has helped me thank you. What code do i put in when im transferring content from merged cells into another sheet where cells are merged?

[@sandychilders9509]

Thank you!! Can you answer a few questions or send me to a video. In my row I have different choices and then 3 columns with different dates ex volunteer signed up for parking on fri, I appreciate your help.

[@michellegygax6527]

This Video tutorial was a godsend. I am only having trouble with one areas, the worksheet I pulled for the data includes a sum and it is not coming over as the sum.

[@SaadKhan-mf9mg]

Thank you for the video...btw if i have a total with the sum formula on the cell...how do I move that to the other sheet as a number rather than the whole formula cell moving...i just get "REF!" in the second sheet...help on this will be appreciated. I have seen your video on paste special...but i dont know how to integrate it within this code.

[@aka7minimouse]

I am looking for help on this as well

[@johandaugaarddrejer9009]

How can this be used, where the copied cells, is inserted as values, so if it is a formula it copies, that it inserts the results, instead of the formula (will be blank when it arrives) :)

[@redhaakhund1271]

👍👍👍👍👍

[@sorasenpie6832]

Great video first of all but I have a question, I have table with multiple entries but only 1 cell with the transfer ID how could i make it so that when I click on the button the transfer ID on cell A1 auto fill with hoe ever many entries there are on the table on column A while everything else goes on column B,C&D while the transfer ID goes on column A
example
ID Item Country QTY
12345 Apples Canada 13
12345 oranges Canada 50
12345 Grapes Canada 16

[@vdcbootcamp5903]

So I utilized this form similar to yours to expense invoices, and then have an invoice log. Everything seems to work once going to my DestCell. Then when it does the offset, it goes to the bottom on my Log where I then have totals. If I enter a 3rd invoice it then replaces the 2nd invoice entered at the bottom of the Log. In other words, it doesn't seem to just go to the next row as seen in the video. Any suggestions?
Here is a copy of that section:
'Create a variable for the paste cell in the Invoice Entry worksheet
Dim DestCell As Range
If InvTrk.Range("A6") = "" Then 'If A6 is empty
Set DestCell = InvTrk.Range("A6") '...then destination cell is A6
Else
Set DestCell = InvTrk.Range("a6").End(xlDown).Offset(1, 0) '...otherwise the next empty row
End If
'If no "InvDate has been entered, exit macro
If InvDate = "" Then
MsgBox "You must enter a Date before adding to the log"
Exit Sub
End If
'Copy and paste data from the Invoice Entry worksheet to the Invoice Tracking worksheet
InvDate.Copy DestCell
InvNo.Copy DestCell.Offset(0, 1)
CoName.Copy DestCell.Offset(0, 2)
RuckUp.Copy DestCell.Offset(0, 3)
Mkt.Copy DestCell.Offset(0, 4)