Microsoft Excel 2007 - VBA To Sort Table And Copy To Anoth..

Currently Challenged ... · External Since: Oct 08, 2009 Posts: 1

I have a table in Microsoft Excel 2007 (TableT - Range A1:D30 on Sheet1 ...
A1 to D1 are the Table Headers) that contains various information on Sheet1.
I would like to Sort TableT where all rows containing an \"X\" in column A
are shown. Then copy those rows and paste them on Sheet2 from A1:A30. Then I
want to sort TableT where all rows with a \"Y\" in column A are shown. Then
copy those rows and paste them on Sheet2 A30:A60. Is there VBA that I can use
to automate this? Thanks for your help.

Gord Dibben · External Since: Feb 23, 2004 Posts: 9431

I don't you want to "sort".

You want to "filter".

Record a macro whilst doing the filtering for "X" or "Y" and doing the
copy and paste

Gord Dibben MS Excel MVP

On Thu, 8 Oct 2009 07:04:03 -0700, Currently Challenged ... <Currently
Challenged ....DeleteThis@discussions.microsoft.com> wrote:

>I have a table in Microsoft Excel 2007 (TableT - Range A1:D30 on Sheet1 ...
>A1 to D1 are the Table Headers) that contains various information on Sheet1.
>I would like to Sort TableT where all rows containing an "X" in column A
>are shown. Then copy those rows and paste them on Sheet2 from A1:A30. Then I
>want to sort TableT where all rows with a "Y" in column A are shown. Then
>copy those rows and paste them on Sheet2 A30:A60. Is there VBA that I can use
>to automate this? Thanks for your help.

Jacob Skaria · External Since: Mar 04, 2009 Posts: 568

Try the below macro

--If the entry is "X" in the code it is referred as double-double quotes
to represent a double quote...Try the below and feedback...

Sub MyMacro()
Dim lngRow As Long, lngRowX As Long, lngRowY As Long
lngRowX = 1: lngRowY = 31
For lngRow = 1 To 30
If Range("A" & lngRow) = """X""" Then
Range("A" & lngRow).Resize(, 4).Copy _
Sheets("Sheet2").Range("A" & lngRowX)
lngRowX = lngRowX + 1
ElseIf Range("A" & lngRow) = """Y""" Then
Range("A" & lngRow).Resize(, 4).Copy _
Sheets("Sheet2").Range("A" & lngRowY)
lngRowY = lngRowY + 1
End If
Next
End Sub

If this post helps click Yes
---------------
Jacob Skaria

"Currently Challenged ..." wrote:

> I have a table in Microsoft Excel 2007 (TableT - Range A1:D30 on Sheet1 ...
> A1 to D1 are the Table Headers) that contains various information on Sheet1.
> I would like to Sort TableT where all rows containing an "X" in column A
> are shown. Then copy those rows and paste them on Sheet2 from A1:A30. Then I
> want to sort TableT where all rows with a "Y" in column A are shown. Then
> copy those rows and paste them on Sheet2 A30:A60. Is there VBA that I can use
> to automate this? Thanks for your help.