Excel question: pasting when data is filtered

[Syringer]

Say I have these two sheets..

1
2
3
4
5

and

1 x
3 y
5 z

So I apply a filter to the first one so that it now is

1
3
5

and want to paste the x, y, z column into there so that it matches up as the 2nd one..but if I paste it'll end up like

1 x
2 y
3 z
4
5

Is there a way to paste the data so that it shows up like the 2nd list?

 

[Syringer]

😕

 

[Syringer]

😕😕

 

[Syringer]

So..Is this possible?

 

[Syringer]

:<

 

[tjcinnamon]

I tried it and it seems it would not allow me to paste on the same sheet but on a different sheet.

Here is some autogenterated excel code to step through to get an idea of how to do it. Although if I have your problem correct all you have to do is:

Make a copy your sheet1 (which would be sheet2)
goto the original sheet1
Apply the filter to sheet 1
copy the column you want from sheet1
Delete the data on sheet2 from the column you copied the data from sheet1 from
paste the copied column from sheet1 to the same, newly deleted, column in sheet2

and:

1 x
2
3 y
4
5 z

goes to:

1 x
3 y
5 z

goes to on sheet2

1 x
2 y
3 z
4
5


Good luck,
JOe K.

P.S. I edited this because I forgot the code:


Sheets("Sheet1").Select
Sheets("Sheet1").Copy Before:=Sheets(1)
Sheets("Sheet1").Select
Range("B1").Select
Selection.AutoFilter Field:=2, Criteria1:="<>"
Columns("B:B").Select
Selection.Copy
Sheets("Sheet1 (2)").Select
Columns("B:B").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("B1").Select
Sheets("Sheet1").Select
Range("B1").Select
ActiveCell.FormulaR1C1 = "x"
Columns("B:B").Select
Selection.Copy
Sheets("Sheet1 (2)").Select
ActiveSheet.Paste