Make a Macro take out rows and add it to new worksheet

V

Vic

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?
 
N

Norman Jones

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.
 
G

Gord Dibben

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
 
V

Vic

Gord Dibben said:
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



Thanks for your quick response. I appreciate it. here's the senario.
Click to expand...
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.
 
V

Vic

Norman Jones said:
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




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.
Click to expand...
your help is greatly appreciated.
 
G

Gord Dibben

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
 
N

Norman Jones

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.
 
V

Vic

Gord Dibben said:
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



Dec. 2, 2005 - 10:18 AM
Click to expand...
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.
 
V

Vic

Norman Jones said:
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






December 2, 2005 - 10:21 AM..
Click to expand...
 
V

Vic

Norman Jones said:
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






December 2, 2005 - 10:21 AM
Click to expand...
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..
 
Top