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:
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
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”).Range(Cells(i, “B”), Cells(i, “F”)).Copy
Sheets(“sheet2”).Range(Cells(j, “D”), Cells(j, “H”)).Select
Application.CutCopyMode = False
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()