Copying data from one sheet to another

[John Pierce]

Start with a WB and one WS with a range of data.
I want to:
Add two new sheets
Check each record on the original sheet and
If Column B in that row = X then
Copy what's in Column A of that row to new Sheet2,
or
If Column B in that row = Y then
Copy what's in Column A of that row to new Sheet 3.

 

[Per Jessen]

Try this:

Sub CopyXY()
Dim Sh1 As Worksheet
Dim shX As Worksheet
Dim shY As Worksheet

Set Sh1 = ActiveSheet
Set shX = Worksheets.Add(after:=Sh1)
Set shY = Worksheets.Add(after:=shX)

Sh1.Activate
FirstRow = 2 'Headings in row 1
LastRow = Range("B" & Rows.Count).End(xlUp).Row

For r = FirstRow To LastRow
If Cells(r, "B").Value = "X" Then
x = x + 1
Cells(r, "A").Copy shX.Cells(x, 1)
ElseIf Cells(r, "B").Value = "Y" Then
y = y + 1
Cells(r, "A").Copy shY.Cells(y, 1)
End If
Next
End Sub

Regards,
Per

 

[John Pierce]

Per,
Thanks, that works for me!

 

[John Pierce]

Here's another one, similar but completely different.
Start with a WB with two existing sheets: Sh1, Sh2
Sh2 contains a lot of permanent records.
Sh1 contains temporary records for transfer to Sh2.
Sh1 has a header row and 7 columns (A-G) and
variable number of records.
I want to copy all records on Sh1 then paste on
Sh2 below existing records, BUT
Sh1 "A" goes in Sh2 "E"
Sh1 "B" goes in Sh2 "C"
Sh1 "C" goes in Sh2 "K"
etc.
I'm thinking copy Sh1 into a Dynamic Array and
then copy to Sh2 element by element?