A few words of thanks would be greatly appreciated.

Excel - Selectively Transfer Data to Pre-defined Range

Latest update on March 3, 2010 at 08:01 AM by aakai1056 .

Issue
Solution
Note

Issue

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.

For Example:
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.

Solution

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

Note

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 workbook 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

This document, titled « Excel - Selectively Transfer Data to Pre-defined Range », is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).

Macro to Compare two sheet with both sheets

Excel VBA - Selecting multiple sheets

Issue

Solution

Note

Subscribe To Our Newsletter!