How to copy and paste when criteria is met
Requirement:
The user is trying to look in column 4 and if the first 3 letters begin with CDR or CAP copy to sheet2.
Solution:
Code:
Option Explicit
Sub SORT_SURFACE_BILLETS()
Dim rData As Range
On Error GoTo err_quit
Application.ScreenUpdating = 0
With Sheets("SHEET1")
If Not .AutoFilterMode Then .Range("A1").AutoFilter
Set rData = .Cells(1).CurrentRegion
rData.AutoFilter Field:=4, Criteria1:="=CDR*", _
Operator:=xlOr, Criteria2:="=CAP*"
With Sheets("SHEET2").Cells(1).CurrentRegion
.Offset(1).Resize(.Rows.Count, 14).ClearContents
End With
.AutoFilter.Range.Resize(, 14).Copy Sheets("SHEET2").Range("A2")
End With
err_quit:
Application.ScreenUpdating = 1
End Sub
If you have problems with the headers:
You could just overwrite the headers:
Code:
.AutoFilter.Range.Resize(, 14).Copy Sheets("SHEET2").Range("A1")
or don't copy the headers
Code:
.AutoFilter.Range.Offset(1).Resize(.AutoFilter.Range.Rows.Count, 14).Copy Sheets("SHEET2").Range("A1")
Obtained from the OzGrid Help Forum.
Solution provided by royUK.
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 information from an excel sheet to a word document |
| How to copy cell value on Sheet 1 to a location in a text in another cell on Sheet 2 |
| How to use a macro or formula to copy data from cell to all cells in that group in adjacent column |
| How to copy specific cells based on a criteria from one sheet to a different sheet |
| How to copy entire row - keeping formulas |
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.
Gallery
