Transfer Transpose Data from one Excel Worksheet to Another Using VBA

I am working in a small call center and when our clients call, we take the basic details like user_name, User_id, phone_number, problem. Now what I want is, when a call comes we will note down all the above mentioned details in to an excel sheet and when I click on update this data should be saved in another excel sheet.
Thanks in advance!
P.S.: What is a variable in Excel VBA?

Download a sample Excel file:

Excel VBA Code for the command button:

Private Sub CommandButton1_Click()
Dim CustomerName As String, CustomerProblem As Integer
CustomerName = Range(“C4”)
CustomerProblem = Range(“C5”)
If Worksheets(“Sheet2”).Range(“B4”).Offset(1, 0) <> “” Then
End If
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = CustomerName
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = CustomerProblem
End Sub

How to share workbooks on a local area network

How to use SKY DRIVE to share files

Watch the Excel training video below to see how data can be tranferred quickly and easily from one excel worksheet into another using Excel VBA:

7 thoughts on “Transfer Transpose Data from one Excel Worksheet to Another Using VBA

  1. Dylan

    Hi! Is there a way to possible extend this code to multiple rows? I have a sample data but when i tried adjusting this code to multiple rows. It doesn’t work. Could you help me?

  2. Fathah

    Hi Sir, Awesome and simple way of teachings. I have a question. I am creating a VBA code to transfer data from sheet1 to sheet2. Sheet1 is having 3 active column. Col C and D. Colum C2:C48 is having products list and D2:D48 is having its Qty and the title of the D is a date. Sheet2 is having same product lists in Column A2:A48 and the title of the Column B1 to P1 is dates of a month. What I want is sheet1.Column D2:D48 value (Qty) to be transferred to sheet2 Column B2:B48 when I click update button. Next day I need the same operation and the Qty should be in next Column (Sheet2. C2:C48) and the value of B2:B48 not to delete.So that I can make one month complete orders and retain for further reference. The orders are generating by the system and I downloading to excel that is sheet1 Column D2:D48, So please solve this, Thanks in advance

  3. Varun Koshal

    Hi, I have a problem I was hoping you could help me with. It is pretty complex and If you want I can email you some sample sheets. I have 16 excel files. Each with the same format and columns, They are basically 16 sheets for 16 territories in the country. Each is filled up manually by the sales reps. Then there is a master sheet. I was all the date from the 16 sheets to get automatically copied on to the master sheet. I tried using a code from one of your videos “Transfer Data from Multiple Workbooks into Master Workbook Automatically” however the data range on this is just for one row Range(“A2:R2”).Copy. each of these 16 sheets will have over 50 rows of data hence we looking at 800 rows overall. Could you please tell me how to make the range such that it copies all the rows and not just row 2. Also everytime I run your code it just keep adding the row 2 from each excel file to the next line of the master file. Hence repeating the same data. As I want this data to be updated live I would need the old data to be deleted each time the macros is run to be updated with fresh data. Could you please advise me as to how this can be done. Please see below the code I have made as described above. Thank you. Look forward to hear from you.

    Sub loopthroughdirectory()
    Dim myfile As String
    Dim erow
    myfile = Dir(“P:\Marketing Analysis\New folder\”)

    Do While Len(myfile) > 0
    If myfile = “master.xlsx” Then
    Exit Sub
    End If

    Workbooks.Open (myfile)

    erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    ActiveSheet.Paste Destination:=Worksheets(“sheet1”).Range(Cells(erow, 1), Cells(erow, 18))

    myfile = Dir


    End Sub

  4. Sandhya

    This code has been working wonders for me, however, it stopped working when I needed to transpose to range “AP1” in my Sheet2. Does this code only work for the columns with single letters? If so, is there a way to edit the code for functionality in double letter columns i.e. AA-IZ? Please help!!


Leave a Reply

Your email address will not be published. Required fields are marked *