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!
Nisha
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
Worksheets(“sheet1”).Select
CustomerName = Range(“C4”)
CustomerProblem = Range(“C5”)
Worksheets(“sheet2”).Select
Worksheets(“Sheet2”).Range(“B4”).Select
If Worksheets(“Sheet2”).Range(“B4”).Offset(1, 0) <> “” Then
Worksheets(“Sheet2”).Range(“B4”).End(xlDown).Select
End If
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = CustomerName
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = CustomerProblem
Worksheets(“Sheet1”).Select
Worksheets(“Sheet1”).Range(“C4”).Select
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:


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

  1. 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. 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. 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)
    Range(“A2:R2”).Copy
    ActiveWorkbook.Close

    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

    Loop

    End Sub

  4. 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!!

  5. Sir, I have two separate excel files (2007 version). In one file, I have only the names as per columns. In another excel file, I have details of the names like Serial, Name (also appearing in the another file just mentioned), fathers name, address and so on. Can I click on the name on the first file and see his details from the second file. Now in the first file there is no serial by which two files can be joined with a unique no. In that case, the second file may show all the names which are similar. I want to know whether it is possible?

  6. Hello Sir
    Regards from Denmark

    How can i delete the data in sheet 1 when it become active again?
    Many thanks in advance..

    Claus

  7. I hope you are fine and doing well.

    I have created a database of downloaded research papers of my interest. I have also hyper-linked those files so that it will be easier for me to open them later on. All hyperlinks are for .pdf files

    I have also added one more feature i.e. use of the auto-filter function to create a dynamic search using macro and displayed the results in another sheet of the same workbook. The macro is running perfectly. However, those cells which were hyperlinked before in the main table are no more linked with my PDF files after the data has been filtered.

    I tried to solve this issue but couldn’t fix it till now.

    Can you please help me to figure out this issue? If you need any additional information, please let me know.

    Best regards,
    Prithvi

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.