Excel Macro issue - How to copy and paste select rows from one sheet to another

[jlsnsw]

Hello everyone.
I am a newbie here and new to creating and modifying macros.

Question: I have a workbook that contains roughly 3500 records an
want to create a macro that copies certain rows and pastes them t
specific sheets in the book based on one criteria, the text in th
cell.
I recorded a macro and it will work for the selected book, but I hav
to do this weekly and the number of rows change from week to week.

Here is the recorded macro:

Selection.AutoFilter Field:=2, Criteria1:="PHC-ACADMIN"
ActiveCell.Rows("1:90").EntireRow.Select
Selection.Copy
Sheets("ACADMIN").Select
Sheets("ACADMIN").Name = "ACADMIN"
ActiveCell.Select
ActiveSheet.Paste

The problem I have, is that the range in ActiveCell.Rows("1:90) change
and pulls data that I do not want in the sheet where is pastes.

Can someone help me.
Thank

 

[Dave Hawley]

Hi,


Try this code

Selection.AutoFilter Field:=2, Criteria1:="PHC-ACADMIN"
ActiveSheet.UsedRange.Copy _
Destination:=Sheets("ACADMIN").Range("A1")


***** Posted via: http://www.ozgrid.com
Excel Templates, Training & Add-ins.
Free Excel Forum http://www.ozgrid.com/forum *****

 

[jlsnsw]

*Hi,
Try this code

Selection.AutoFilter Field:=2, Criteria1:="PHC-ACADMIN"
ActiveSheet.UsedRange.Copy _
Destination:=Sheets("ACADMIN").Range("A1")


***** Posted via: http://www.ozgrid.com
Excel Templates, Training & Add-ins.
Free Excel Forum http://www.ozgrid.com/forum ***** *

that worked perfectly.
Thanks for the help. You just saved me 15-20 minutes of wor