Transfer Specific Excel Worksheet Data Range from one Worksheet to another


A website visitor wants to know how to Transfer Specific Excel Worksheet Data Range from one Worksheet to another based on a condition or criteria using VBA. Here is his specific question:

How would I write VBA code or Macro to verify a Customer ID on one sheet copy some data from that row and place it on another sheet that has a list of the same Customer IDs. like IF CID on sheet 1 = CID on sheet 2 then copy data in Columns Q,R,S,T and put it on sheet 2 in Columns J,K,L,M.

Thanks any help wold be great I have around 7000 Customer Id numbers from last year and want to transfer the data from last years columns to this years sheets. There are more customers and some data is on old sheets and not on new ones. so I have to have a program or Macro read the CID in Column C of one sheet and copy certain data from certain rows and place it into the new sheets this year. Commented by George M.

Let’s see how we can solve this interesting and practical problem quickly and easily using a macro or VBA code. Let’s analyze the problem first.

  • Data is in two different sheets
  • Data in one sheet needs to be updated using the data in another sheet
  • Only specific data needs to be updated
  • The ID or name should match in both the sheets and only then the data should be updated

So we need to compare, let’s say, a string of one worksheet cell with another cell in another worksheet. To do this we need a looping process.

Since we will compare one string type of data will all the data in the other sheet, we need to have a second looping process. This means we should use a nested loop – a loop within a loop.

Now we do not know how much data is entered in each of the worksheets. So we need to define and determine the last row used for data entry in each of the sheets.

Also when we shuttle between the two worksheets we must ensure that we activate the correct worksheet when we are copying or pasting data in that worksheet.

Watch the video below and then study the complete VBA code given below the video:


Download sample file by clicking on the Excel icon:

 Watch this video on YouTube

Sub transfer()
Dim i As Long, j As Long, lastrow1 As Long, lastrow2 As Long
Dim myname As String
lastrow1 = Sheets(“sheet1”).Range(“A” & Rows.Count).End(xlUp).Row

For i = 2 To lastrow1
myname = Sheets(“sheet1”).Cells(i, “A”).Value

Sheets(“sheet2”).Activate
lastrow2 = Sheets(“Sheet2”).Range(“A” & Rows.Count).End(xlUp).Row

For j = 2 To lastrow2

If Sheets(“sheet2”).Cells(j, “A”).Value = myname Then
Sheets(“sheet1”).Activate
Sheets(“sheet1”).Range(Cells(i, “B”), Cells(i, “F”)).Copy
Sheets(“sheet2”).Activate
Sheets(“sheet2”).Range(Cells(j, “D”), Cells(j, “H”)).Select
ActiveSheet.Paste
End If

Next j
Application.CutCopyMode = False
Next i
Sheets(“sheet1”).Activate
Sheets(“sheet1”).Range(“A1”).Select
End Sub

Finally we insert a command button in Sheet1 and use the click event to call the macro ‘transfer’ as shown below:

Private Sub CommandButton1_Click()
transfer
End Sub

22 thoughts on “Transfer Specific Excel Worksheet Data Range from one Worksheet to another

  1. ZAHEER

    Dear sir , I am very thankful to you. You help a lot to learn excel

    Sir I have question , I have 3 workbooks which contains different data with same headings, I have issue worksheet of each where i type a unique value and look up the row values,

    I want to lookup value in another workbook which search values form three of them and placed with unique code. Is it possible?

    Reply
  2. James

    Dear Sir,

    I need to transfer master data into a form format in another file name.

    In the form format there are limited lines to input the data I wish to auto input so if my master has more data to pick up and cannot fit into the first form, it will auto create a second form. Not sure can this be done. Pls help

    Reply
  3. CHANDRAJIT DUTTA

    Sir,
    I want to copy a range of data from one worksheet(Sheet1) to another worksheet(Sheet2) in excel, in which some cells in Sheet1 contains some formulas.I want to copy the values from the cells of Sheet1 to Sheet2.I created a button with vba code by which i can transfer all the data from Sheet1 to Sheet2 and the cells of Sheet1 has been reseted and blank. But at the time of transfer the data all the formulas has also been reseted.I want a help so that i can transfer only the values from the formulated cells without resetting the formulas.

    Reply
  4. JANDON

    I only need to copy data in Columns Q,S,T from sheet 1 if CID match the name on sheet 2, then copy the match data to sheet 2, Column K,L,M.Please help how to modify the vba code?

    Reply
  5. Kaiho

    Dear Sir,

    Please guide me on this, I have few worksheets and the first worksheet is the calendar and date of holidays (B11:B25), the rests are semi monthly timesheet for workers. The date are from (C4:U4) in timesheets. if the date is same with the date of holidays in sheet1 then i need to insert “PH” under the date, and keep the rests as “Nm”, when the date change to another month, the text will change automatically as well. is this possible by using macro??

    Reply
  6. karishma

    Sir,
    I used the given code provided by you. but it is showing subscript(9) error in line 4
    “lastrow1 = Sheets(“sheet1”).Range(“A” & Rows.Count).End(xlUp).Row”
    can you please help me in solving this.

    Reply
  7. khimseily

    Dear Sir,

    i do same as yours, as i want to transfer data from sheet1 (rename) to sheet2 (Rename)
    but there is cant work.

    please advise

    Reply
  8. khimseily

    Sub transfer()
    Dim i As Long, j As Long, lastrow1 As Long, lastrow2 As Long
    Dim myname As String
    lastrow1 = Sheets(“Input”).Range(“A” & Rows.Count).End(xlUp).Row (erro) this one

    For i = 2 To lastrow1
    myname = Sheets(“Input”).Cells(i, “A”).Value

    Sheets(“Database”).Activate
    lastrow2 = Sheets(“Database”).Range(“A” & Rows.Count).End(xlUp).Row

    For j = 2 To lastrow2

    If Sheets(“Database”).Cells(j, “A”).Value = myname Then
    Sheets(“Input”).Activate
    Sheets(“Input”).Range(Cells(i, “A”), Cells(i, “K”)).Copy
    Sheets(“Database”).Activate
    Sheets(“Database”).Range(Cells(j, “A”), Cells(j, “K”)).Select
    ActiveSheet.Paste
    End If

    Next j
    Application.CutCopyMode = False
    Next i
    Sheets(“Input”).Activate
    Sheets(“Input”).Range(“A1”).Select

    End Sub

    Reply
  9. Yusuf

    Its corrupt workbook problem (maybe) @khimseily question. I have same problem (open Excel 2013 to 2010). I tried to save as but still getting error “run time error 32809 application defined or object defined error”. Then, I tried to copy data from Sheet 1 and Sheet 2 in new Workbook. At last, copying scripts and running. Work perfectly for me.
    Hope this help..

    Reply
  10. farhan

    Dear Mr. Dinesh,
    I need to ask you that how can i sum specific products value based on condition. Kindly tell me where should i send you a sample of the report so that you shall help on it, thanks

    Regards

    Reply
  11. Mohammed Shabaz Athar

    Hi, Sir

    I really appreciate your work for spreading the knowledge and your experiences with out cost… I have a question which I am struggling to resolve.

    I am working for Saudi Base Company in Hr department we have 900 employees out of that monthly employees go for Vacation where we have to pay the fees to Govt. when ever I am paying certain amount to define employee then I have to reimbursement from our finance dept. Now my I need such sheet where all employee data is stored in sheet 1 and i need to transfer some information from sheet1 to sheet2 with unique code which is our employee number.

    Hope it will be answered by You

    Many Thanks

    Reply
  12. Prithviraj.s

    Sir,
    Thanks, Run time error 9 coming, while running the macro, and high lighted the below line, lastrow1 = Sheets(“Issue”).Range(“A” & Rows.Count).End(xlUp).Row.
    please help

    Reply
  13. Pingback: Transfer Specific Worksheet Range Data from one worksheet to another based on condition | Good 4K

  14. Pingback: Transfer Specific Worksheet Range Data from one worksheet to another based on condition | zhubo 22

  15. Jamsheed

    Sub transfer()
    Dim i As Long, j As Long, lastrow As Long, lasrow2 As Long
    Dim myname As String
    lastrow1 = Sheets(“sheet1”).Range(“A” & Rows.Count).End(xlUp).Row
    For i = 2 To lastrow
    myname = Sheets(“sheet1”).Cells(i, “B”).Value
    lastrow2 = Sheets(“sheet2”).Range(“A” & Rows.Count).End(xlUp).Row
    For j = 2 To lastrow2
    If Sheets(“sheet2”).Cells(j, “B”).Value = myname Then
    Sheets(“sheet1”).Activate
    Sheets(“sheet1”).Range(Cells(i, “E”)).Copy (error on this line) i want only only one column
    Sheets(“sheet2”).Activate
    Sheets(“sheet2”).Range(Cells(j, “E”)).Select and past in against matching cell row
    ActiveSheet.Paste
    End If
    Next j
    Application.CutCopyMode = False
    Next i
    Sheets(“sheet1”).Activate
    Sheets(“sheet1”).Range(“A1”).Select
    End Sub

    Reply
    1. Zahid Uppal

      Hi, Jamsheed, I have the same Problem. I need to copy only one column same error. If you resolved this issue. Could you please forward to me the solution…. Thank you very much

      Zahid Uppal

      zuppal@hotmail.ca

      Reply
  16. Jamsheed

    Sheets(“sheet1”).Range(Cells(i, “E”)).Copy
    error on this line i want only one matching row+column and paste in against matching row

    Reply
  17. thash

    i downloaded your file and try to run the macros, but i am getting run time error “9” subscript out of range message , what could be the reason? i didn’t modify anything to the file you made. also instead of name the condition is date , what is the variable i should define. appropriate your help

    Reply
  18. V.Srinivasa Rao

    Sir, I have a similar Problem. I have a Pivot table data with Drugs sold on different dates with Drugs in Rows (in Column A) & Different Dates headings in Columns B to Last Column. Against each Row & Column we get Qty of drugs sold. [1)Name of Drugs sold in Column A 2) Against each row contains Qty sold on Different Dates ( Dates contain 1- 31 in different columns. Variable i.e., some dates may be absent when they are not sold Eg Sundays). Against each Drug & Date we get Qty sold.
    Using VBA, I have to copy data from this pivot table in Sheet2 to Similar table in sheet1 against each Matched Cells where all the dates from 1-31 are present in the columns (unlike in Pivot table-where some dates & drugs are absent) & All the drugs which are not sold also are present in the Rows in Column A in Sheet1. Kindly Help. Thank U sir

    Reply
  19. SIVABALAN V

    Sir, Please can show me to copy only particular columns through this code from one sheet another sheet meeting some row criteria.

    Reply

Leave a Reply

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