How to copy specific cells based on a criteria from one sheet to a different sheet
Category: [Excel] Demo Available
How to copy specific cells based on a criteria from one sheet to a different sheet
Requirement:
The user is trying to copy data from the tab called Data to the tab called Reports using Excel 2013.
The user needs a looping sequence that looks at the table located on the Reports tab. Compares the value in column C of the Reports tab to the data located in Column D of the Data tab. If a match is found, then copy the data from Column G and H of the Data tab to column G and H of the Reports tab.
Solution:
Code:
Private Sub CommandButton1_Click()
Dim i As Long, j As Long, lastrow1 As Long, lastrow2 As Long
Dim Reports_INVNum As String
Dim Data_INVNum As String
lastrow1 = Sheets("Data").Range("D" & Rows.Count).End(xlUp).Row
For i = 5 To lastrow1
Data_INVNum = Sheets("Data").Cells(i, "D").Value
lastrow2 = Sheets("Reports").Range("C" & Rows.Count).End(xlUp).Row
For j = 5 To lastrow2
If Sheets("Reports").Cells(j, "C").Value = Data_INVNum Then
Sheets("Data").Range("H" & i & ":I" & i).Copy Sheets("Reports").Range("G" & j)
End If
Next j
Application.CutCopyMode = False
Next i
End Sub
Obtained from the OzGrid Help Forum.
Solution provided by Carim.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to copy entire row - keeping formulas |
| How to cut, copy and insert on a loop |
| How to use a macro to copy data from multiple workbooks to one master sheet in another workbook |
| How to copy data from multiple workbooks into one |
| How to copy the entire sheet and paste as values - running on multiple tabs |
| How to list & display all files in user folder, select file and copy specific tab into master sheet |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.