Filter and Copy to secondary sheet
Posted by Jeanne on May 03, 2000 5:47 PM
I have an Excel 2000 file that is used to track sales which contains information regarding service provided, price, etc. After a month's entries are posted to the first worksheet (called dailylog), the rows are sorted by Customer Name. I have setup the Automatic Filter and have done a "record macro" to filter, copy, paste to secondary sheet, return to dailylog and undo the filter, but this macro will have to be changed quite often as the next month's dailylog sheet may not have the same amount of entries for each customer. Is there a way to have Excel look for a particular text as the customer name (listed in column A), grab ONLY the rows that contain that text, and then copy the rows to a secondary sheet (customer name sheet) to track the monthly items for a particular customer? Please help!! Thank you !!
Posted by Jeanne on May 03, 2000 5:57 PM
PS..I have tried the advanced filter, won't copy to a secondary sheet
Posted by Celia on May 04, 2000 9:50 PM
Jeanne
Try the following macro. Its a quick fix there is probably a more efficient way of doing it and its not very sophisticated (e.g. an input box could be used instead of a cell on the DailyLog sheet, there is no error handling code, etc.). It can always be polished up later if it does what you are looking for.
It has been assumed that the customer names on the DailyLog start at cell A3.
Before running the macro, you do not have to do any filtering or sorting but you must type into cell A1 the customer name for whom you want the data pasted.
(You can use another cell if you wish and change the code accordingly.)
Please also note that the spelling, spacing, etc. of a customer name must be exactly the same in every place it appears i.e. in cell A1, in the customer list on the DailyLog, AND the name of the customer sheet.
Sub PasteToCustSheet()
Dim C As Range
Sheets("DailyLog").Activate
For Each C In Range(Range("A3"), Range("A65536").End(xlUp))
If C.Value = Range("A1").Value Then
C.EntireRow.Copy
ActiveSheet.Paste ThisWorkbook.Sheets(C.Value).Range("a65536").End(xlUp).Offset(1, 0)
End If
Next
Application.CutCopyMode = False
End Sub
Celia
Posted by Jeanne on May 08, 2000 8:14 PM
Thanks but....
Celia,
Thank you for your response but this line of code is giving me trouble...
ActiveSheet.Paste ThisWorkbook.Sheets(C.Value).Range("a65536").End(xlUp).Offset(1, 0)
Is there any clarification that I need to make to this line in order for this macro to work?
Thanks for all your help!!
Jeanne
Posted by Ivan Moala on May 09, 2000 12:40 AM
Re: Thanks but....
Jeanne
I think the line should read;
ActiveSheet.Paste ThisWorkbook.Sheets("DailyLog").Range("a65536").End(xlUp).Offset(1, 0)
Ivan ,
Posted by Celia on May 13, 2000 5:20 PM
Re: Thanks but....
Jeanne
I've tested the code and it works.
The problem must be that you are entering a customer name in cell A1 on the DailyLog but you do not have a customer sheet with exactly the same name.
You first need to create a sheet for each customer and the name of each sheet must be exactly the same as the name entered in cell A1 of the DailyLog.
Celia ,
