If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Microsoft Excel 2007 - VBA To Sort Table And Copy To Another Sheet
I have a table in Microsoft Excel 2007 (TableT - Range A130 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. |
#2
|
|||
|
|||
Microsoft Excel 2007 - VBA To Sort Table And Copy To Another Sheet
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 wrote: I have a table in Microsoft Excel 2007 (TableT - Range A130 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. |
#3
|
|||
|
|||
Microsoft Excel 2007 - VBA To Sort Table And Copy To Another Sheet
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 A130 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. |
Thread Tools | |
Display Modes | |
|
|