Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy to another sheet based on column value
Hi, Im trying to find a way (im guessing a macro) that I can copy all the
rows on a page that have "RES" as the value in the b column to another sheet, pasting them starting in the first row that is open on the new sheet. Can anyone help with this? It would be greatly appreciated. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy to another sheet based on column value
Do you want a new sheet or do you want to append to the first blank row of an
existing sheet? "Mike" wrote: Hi, Im trying to find a way (im guessing a macro) that I can copy all the rows on a page that have "RES" as the value in the b column to another sheet, pasting them starting in the first row that is open on the new sheet. Can anyone help with this? It would be greatly appreciated. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy to another sheet based on column value
Hi mike
Try this http://www.rondebruin.nl/copy5.htm Or this Add-in http://www.rondebruin.nl/easyfilter.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Mike" wrote in message ... Hi, Im trying to find a way (im guessing a macro) that I can copy all the rows on a page that have "RES" as the value in the b column to another sheet, pasting them starting in the first row that is open on the new sheet. Can anyone help with this? It would be greatly appreciated. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy to another sheet based on column value
Here is some code that creates a new sheet for you
Public Sub CopyRows() Dim wksToSearch As Worksheet Dim wksToPaste As Worksheet Dim strToFind As String Dim rngToSearch As Range Dim rngFound As Range Dim rngPaste As Range Dim strFirstAddress As String strToFind = "RES" Set wksToSearch = ActiveSheet Set rngToSearch = wksToSearch.Range("B1").EntireColumn Set wksToPaste = Worksheets.Add Set rngPaste = wksToPaste.Range("A1") Set rngFound = rngToSearch.Find(strToFind, , , xlPart) If Not rngFound Is Nothing Then strFirstAddress = rngFound.Address Do rngFound.EntireRow.Copy rngPaste Set rngFound = rngToSearch.FindNext(rngFound) Set rngPaste = rngPaste.Offset(1, 0) Loop Until rngFound.Address = strFirstAddress End If End Sub If this is a one of thing then doing a filter is going to be the easiest though... HTH "Jim Thomlinson" wrote: Do you want a new sheet or do you want to append to the first blank row of an existing sheet? "Mike" wrote: Hi, Im trying to find a way (im guessing a macro) that I can copy all the rows on a page that have "RES" as the value in the b column to another sheet, pasting them starting in the first row that is open on the new sheet. Can anyone help with this? It would be greatly appreciated. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy to another sheet based on column value
Just in case you would have been happy with any solution, you also had the
option of simply sorting the data on that column then copying the block that meets your criteria. You could also just filter on Col, B, use Edit / Go To / Special Cells / Visible and then copy them to your new sheet. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "Mike" wrote in message ... Hi, Im trying to find a way (im guessing a macro) that I can copy all the rows on a page that have "RES" as the value in the b column to another sheet, pasting them starting in the first row that is open on the new sheet. Can anyone help with this? It would be greatly appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy rows from one Data sheet to another sheet based on cell conte | Excel Discussion (Misc queries) | |||
Search for rows in one sheet and copy into another sheet based on customer id | Excel Worksheet Functions | |||
Copy rows of one sheet into mutiple sheets based on column value | Excel Discussion (Misc queries) | |||
MACRO - copy rows based on value in column to another sheet | Excel Discussion (Misc queries) | |||
how to find and copy values on sheet 2, based on a list on sheet 1 | Excel Programming |