| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Make a Macro take out rows and add it to new worksheet
Hoe do i set up a Macro to look for certain information on a worksheet then
take it out and open a new worksheet and paste it there? Is this possible? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Make a Macro take out rows and add it to new worksheet
Hi Vic,
Try using the Autofilter feature. Turn on the macro recorder while you perform the required operations. This will provide you with code which can be edited to enable general application. If you have any problems with such editing, post back with details. --- Regards, Norman "Vic" wrote in message ... Hoe do i set up a Macro to look for certain information on a worksheet then take it out and open a new worksheet and paste it there? Is this possible? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Make a Macro take out rows and add it to new worksheet
Yes, it is possible but "certain information" is a bit vague.
Does this information occur once, more than once, randomly distributed, in one cell or a range of cells? Gord Dibben Excel MVP On Thu, 1 Dec 2005 10:19:04 -0800, Vic wrote: Hoe do i set up a Macro to look for certain information on a worksheet then take it out and open a new worksheet and paste it there? Is this possible? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Make a Macro take out rows and add it to new worksheet
"Gord Dibben" wrote: Yes, it is possible but "certain information" is a bit vague. Does this information occur once, more than once, randomly distributed, in one cell or a range of cells? Gord Dibben Excel MVP On Thu, 1 Dec 2005 10:19:04 -0800, Vic wrote: Hoe do i set up a Macro to look for certain information on a worksheet then take it out and open a new worksheet and paste it there? Is this possible? Thanks for your quick response. I appreciate it. here's the senario. i receive a bank statement everyday and it comes in by "pool" i deal with certain pools from there. I have to cut the row from my "pool" and paste it to an empty worksheet, so what i want to do is to have excel automatically look for just my "Pool" and cut the whole row and open a new worksheet then paste it there. now i have 3 different accounts so i want to set it up for just my pools which is "pool-3" & "pool-4" in one worksheet then "pool-23" & "pool 24" in another worksheet. I do this only once a day in the morning. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Make a Macro take out rows and add it to new worksheet
"Norman Jones" wrote: Hi Vic, Try using the Autofilter feature. Turn on the macro recorder while you perform the required operations. This will provide you with code which can be edited to enable general application. If you have any problems with such editing, post back with details. --- Regards, Norman "Vic" wrote in message ... Hoe do i set up a Macro to look for certain information on a worksheet then take it out and open a new worksheet and paste it there? Is this possible? Norman thanks for your quick response. I did the filter and it worked but one problem is when i tried it for a previous day it looked for the rows that i highlighted and this messed it up. there are times that i might have 50 rows and otehr times i might have 100 rows so i need to set it up where after i do the filter it will catch all the lines that are there. your help is greatly appreciated. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Make a Macro take out rows and add it to new worksheet
Vic
See Norman's post about using autofilter and the macro recorder. Will probably get you what you need. Post back with the code generated if you need more help. Gord On Thu, 1 Dec 2005 11:21:03 -0800, Vic wrote: "Gord Dibben" wrote: Yes, it is possible but "certain information" is a bit vague. Does this information occur once, more than once, randomly distributed, in one cell or a range of cells? Gord Dibben Excel MVP On Thu, 1 Dec 2005 10:19:04 -0800, Vic wrote: Hoe do i set up a Macro to look for certain information on a worksheet then take it out and open a new worksheet and paste it there? Is this possible? Thanks for your quick response. I appreciate it. here's the senario. i receive a bank statement everyday and it comes in by "pool" i deal with certain pools from there. I have to cut the row from my "pool" and paste it to an empty worksheet, so what i want to do is to have excel automatically look for just my "Pool" and cut the whole row and open a new worksheet then paste it there. now i have 3 different accounts so i want to set it up for just my pools which is "pool-3" & "pool-4" in one worksheet then "pool-23" & "pool 24" in another worksheet. I do this only once a day in the morning. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Make a Macro take out rows and add it to new worksheet
Hi Vic,
You will need to edit the recorded code to enable use with varying data groups. If you post the code you are using, there are numerous people who would be prepared to suggest appropriate amendments. --- Regards, Norman "Vic" wrote in message ... "Norman Jones" wrote: Hi Vic, Try using the Autofilter feature. Turn on the macro recorder while you perform the required operations. This will provide you with code which can be edited to enable general application. If you have any problems with such editing, post back with details. --- Regards, Norman "Vic" wrote in message ... Hoe do i set up a Macro to look for certain information on a worksheet then take it out and open a new worksheet and paste it there? Is this possible? Norman thanks for your quick response. I did the filter and it worked but one problem is when i tried it for a previous day it looked for the rows that i highlighted and this messed it up. there are times that i might have 50 rows and otehr times i might have 100 rows so i need to set it up where after i do the filter it will catch all the lines that are there. your help is greatly appreciated. |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Make a Macro take out rows and add it to new worksheet
"Gord Dibben" wrote: Vic See Norman's post about using autofilter and the macro recorder. Will probably get you what you need. Post back with the code generated if you need more help. Gord On Thu, 1 Dec 2005 11:21:03 -0800, Vic wrote: "Gord Dibben" wrote: Yes, it is possible but "certain information" is a bit vague. Does this information occur once, more than once, randomly distributed, in one cell or a range of cells? Gord Dibben Excel MVP On Thu, 1 Dec 2005 10:19:04 -0800, Vic wrote: Hoe do i set up a Macro to look for certain information on a worksheet then take it out and open a new worksheet and paste it there? Is this possible? Thanks for your quick response. I appreciate it. here's the senario. i receive a bank statement everyday and it comes in by "pool" i deal with certain pools from there. I have to cut the row from my "pool" and paste it to an empty worksheet, so what i want to do is to have excel automatically look for just my "Pool" and cut the whole row and open a new worksheet then paste it there. now i have 3 different accounts so i want to set it up for just my pools which is "pool-3" & "pool-4" in one worksheet then "pool-23" & "pool 24" in another worksheet. I do this only once a day in the morning. Dec. 2, 2005 - 10:18 AM Thanks for your quick response. Here is the Macro Cells.Select Cells.EntireColumn.AutoFit Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="3" Rows("1105:1333").Select Selection.Cut Sheets("DMAFILED0548D").Select Sheets.Add ActiveSheet.Paste Sheets("DMAFILED0548D").Select Selection.AutoFilter Field:=1, Criteria1:="4" Rows("1336:1436").Select Selection.Cut Sheets("Sheet1").Select ActiveWindow.SmallScroll Down:=201 Range("A230").Select ActiveSheet.Paste Sheets("Sheet1").Select Sheets("Sheet1").Name = "Separate" Sheets("DMAFILED0548D").Select Selection.AutoFilter Field:=1, Criteria1:="23" Rows("1475:1479").Select Selection.Cut Sheets("DMAFILED0548D").Select Sheets.Add ActiveSheet.Paste Sheets("DMAFILED0548D").Select Selection.AutoFilter Field:=1, Criteria1:="24" Rows("1482:1548").Select Selection.Cut Sheets("Sheet2").Select Range("A6").Select ActiveSheet.Paste Sheets("Sheet2").Select Sheets("Sheet2").Name = "Pru-Arizona" Cells.Select Cells.EntireColumn.AutoFit Sheets("Separate").Select Cells.Select Range("A202").Activate Cells.EntireColumn.AutoFit End Sub You see in the "ROWS" line it shows the rows I highlighted to cut, now today it might be that amount of lines but tmrw might be a smaller amount or a greater amount of lines i need to cut into my new worksheet. |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Make a Macro take out rows and add it to new worksheet
"Norman Jones" wrote: Hi Vic, You will need to edit the recorded code to enable use with varying data groups. If you post the code you are using, there are numerous people who would be prepared to suggest appropriate amendments. --- Regards, Norman "Vic" wrote in message ... "Norman Jones" wrote: Hi Vic, Try using the Autofilter feature. Turn on the macro recorder while you perform the required operations. This will provide you with code which can be edited to enable general application. If you have any problems with such editing, post back with details. --- Regards, Norman "Vic" wrote in message ... Hoe do i set up a Macro to look for certain information on a worksheet then take it out and open a new worksheet and paste it there? Is this possible? Norman thanks for your quick response. I did the filter and it worked but one problem is when i tried it for a previous day it looked for the rows that i highlighted and this messed it up. there are times that i might have 50 rows and otehr times i might have 100 rows so i need to set it up where after i do the filter it will catch all the lines that are there. your help is greatly appreciated. December 2, 2005 - 10:21 AM.. |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Make a Macro take out rows and add it to new worksheet
"Norman Jones" wrote: Hi Vic, You will need to edit the recorded code to enable use with varying data groups. If you post the code you are using, there are numerous people who would be prepared to suggest appropriate amendments. --- Regards, Norman "Vic" wrote in message ... "Norman Jones" wrote: Hi Vic, Try using the Autofilter feature. Turn on the macro recorder while you perform the required operations. This will provide you with code which can be edited to enable general application. If you have any problems with such editing, post back with details. --- Regards, Norman "Vic" wrote in message ... Hoe do i set up a Macro to look for certain information on a worksheet then take it out and open a new worksheet and paste it there? Is this possible? Norman thanks for your quick response. I did the filter and it worked but one problem is when i tried it for a previous day it looked for the rows that i highlighted and this messed it up. there are times that i might have 50 rows and otehr times i might have 100 rows so i need to set it up where after i do the filter it will catch all the lines that are there. your help is greatly appreciated. December 2, 2005 - 10:21 AM Thanks for your quick response.. Here is my macro Cells.Select Cells.EntireColumn.AutoFit Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="3" Rows("1105:1333").Select Selection.Cut Sheets("DMAFILED0548D").Select Sheets.Add ActiveSheet.Paste Sheets("DMAFILED0548D").Select Selection.AutoFilter Field:=1, Criteria1:="4" Rows("1336:1436").Select Selection.Cut Sheets("Sheet1").Select ActiveWindow.SmallScroll Down:=201 Range("A230").Select ActiveSheet.Paste Sheets("Sheet1").Select Sheets("Sheet1").Name = "Separate" Sheets("DMAFILED0548D").Select Selection.AutoFilter Field:=1, Criteria1:="23" Rows("1475:1479").Select Selection.Cut Sheets("DMAFILED0548D").Select Sheets.Add ActiveSheet.Paste Sheets("DMAFILED0548D").Select Selection.AutoFilter Field:=1, Criteria1:="24" Rows("1482:1548").Select Selection.Cut Sheets("Sheet2").Select Range("A6").Select ActiveSheet.Paste Sheets("Sheet2").Select Sheets("Sheet2").Name = "Pru-Arizona" Cells.Select Cells.EntireColumn.AutoFit Sheets("Separate").Select Cells.Select Range("A202").Activate Cells.EntireColumn.AutoFit End Sub you see in the "ROWS" lines it shows the line numbers I highlighted, now tmrw it might be more lines i need to bring over to the other worksheet or it might be less lines. that's my problem.. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
