I am new to Excel VB. I'd like to compare a set of data from worksheet1 by meeting a set of multiple conditions/criteria's and then transfer this data to a pre-defined range in Worksheet2 all the while omitting columns of data that I don't need.
Worksheet1 contains the following data where I need data that meet the value "si" in column A and also value "d" and "m" in column B and then transfer the corresponding rows in Column A,C and E only to worksheet2.
A B C D E si d o s xxxxxx si d o 2 ddddd pg d r x dddd pg m r a sdfsdf fj m w 3 ghghgh fj d w f dssdg si m x 1 sfsdfsd si y x 1 hmhjkk
Worksheet2 has the pre-defined columns/range (A, C and E) to copy the data into. If this works I should have the following result as shown below.
A C E si o xxxxxx si o ddddd si x sfsdfsd
Note that the predefined columns or ranges in worksheet2 should automatically expand and contract automatically according to the amount of data transferred into it.
Any method is fine as long as the out put in Worksheet 2 is archived.
The data in sheet 1 must have column headings in row 1. the sample sheet will be like this:
hdng1 hdng2 hdng3 hdng4 hdng5 si d o s xxxxxx si d o 2 ddddd pg d r x dddd pg m r a sdfsdf fj m w 3 ghghgh fj d w f dssdg si m x 1 sfsdfsd si y x 1 hmhjkk
run this macro on this and see sheet 2
(always keep original file safely somewhere
the macro is
Sub test() Worksheets("sheet2").Cells.Clear Worksheets("sheet1").Activate ActiveSheet.UsedRange.Select Selection.AutoFilter Field:=1, Criteria1:="si" Selection.AutoFilter Field:=2, Criteria1:="=d", Operator:=xlOr, _ Criteria2:="=m" ActiveSheet.Cells.SpecialCells(xlCellTypeVisible).Copy With Worksheets("Sheet2") .Range("A1").PasteSpecial .Range("B:B,D:D").Delete shift:=xlToLeft Application.CutCopyMode = False End With Selection.AutoFilter Range("A1").Select End Sub
Thanks to [https://ccm.net/forum/affich-274345-selectively-transfer-data-topre-defined-range venkat1926 for this tip on the forum.
- Excel - Selectively Transfer Data to Pre-defined Range
- Selectively Transfer Data toPre defined Range ✓ - Forum - Office Software
- Excel formula transfer data between worksheets - How-To - Excel
- Excel vba transfer data from one sheet to another - How-To - Excel
- Excel automatically transfer data to another sheet - How-To - Excel
- Transfer data from one excel worksheet to another automatically ✓ - Forum - Excel