Selectively Transfer Data toPre defined Range

Solved/Closed
Yausi - Feb 26, 2010 at 04:44 AM
venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
- Feb 26, 2010 at 08:00 PM
Hello,

I am new to Excel VB. I'd like to compare a set of data from worksheet1 by meeting a set of multiple conditions/criterias and then transfer this data to a pre-defined range in Worksheet2 all the while omiting 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 tranfer the corespondind 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 transaferred into it.

Any method is fine as long as the out put in Worksheet 2 is archived.

Thanks,

Yausi,
Lost in Excel VB jungle...

3 replies

venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
810
Feb 26, 2010 at 06:25 AM
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
1
yausi
Posts
1
Registration date
Friday February 26, 2010
Status
Member
Last seen
February 26, 2010

Feb 26, 2010 at 07:14 AM
Thanks Venkat1926. Much apreciated. At least you have made me progress a litlle.

I have copied the macro and ran it on worksheet1. That is, I called the macro while in active sheet, Worksheet1. I have however encountered this error message "Run-time error '1004': PasteSpecial of Range class failed" and when I chose "Debug" from the VB dialogue box the line ".Range("A1").PasteSpecial" was highlighted in yellow.

Any suggestions how I can overcome this error?
0
venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
810
Feb 26, 2010 at 08:00 PM
I have once again checked. the macro completely works for me. I know in yourworkbook sheet2 is available otherwise it would have stopped in the line "with worksheets("sheet2")" and the error message would have been "subscription out of range". What is your version of excel. mine excel 2002(windows xp).

I suggest you delete the macro and once again COPY PASTE (not type) the macro in the vb editor and again run the macro give me feedback
0