Auto-copy of select rows to another worksheetSolved/Closed

Question

Hello, 

This is my first time asking a question, so I will do my absolute best to follow and respect the forum guidelines and it's contributors.  I am not a total newbie when it comes to Excel and do surprise myself on what I can learn from great forums like this one.  I am pretty good at formula writing and just now diving into Macros (mostly with the help of internet forums and articles). 

For the record, I am using Excel 2003, the workbook I am currently working on has 13 sheets, one for each month of the year and then a yearly summary report sheet.

With that said, here is the issue I would love to see if one of you Excel stars can help me with:

This workbook will be used to track patient status as they move through a 6 step study process.  Many patients will move through this process within a month, however, many will not and hence will straddle one, two, and maybe even three months and hence will need their tracking information moved from one worksheet to another (month to month).  

I could do this manually, but I would like to see if there is way to have Excel automatically copy any unfinished patient's row of information to the next worksheet at the first of the month.  There is a column for each step where the finish date of the step is entered.  Using the empty date cell of the 6th step could be used as a reference for any coding.

Hopefully, that was not too long-winded and included all pertinent information.  I would list the beginnings of a Macro, but don't even know where to begin.

Any help is of course greatly appreciated, even if it's just pointing me in the right direction.

Thank you.


Configuration: Windows / Internet Explorer 6.0

venkat1926 · Answer

please post a small extract of one monthly sheet and explain what you want to do with respect to that sheet.

Big.Moe · Answer

Thank you so much for your reply.

Could I ask you how I would go about posting part of my worksheet?
Is there a way to attach a file or paste an image into the reply?

When I paste the text from the worksheet it is too wide to fit into the box to properly show the layout.

Thanks.

venkat1926 · Answer

this newsgroup does not have facility to post a data sheet.  but you can upload in some third party like "speedyshare.com" and post the webpage of the upload.

Big.Moe · Answer

Thank you so much for that suggestion, I should of thought of that myself.  

Here is the link to that file:
http://speedy.sh/rpWPQ/Tracking-List.xlsx

As you will see each patient has it's own row and as they enter each stage (columns C, E, G, I, K, M) someone enters the date, then the Excel sheet generates how many days it took for the patient to that stage for internal efficiency tracking.  This keeps going until the patient is finished and out of the system.

However, the issue becomes when patients straddle months and hence straddle multiple worksheets.  I would like to see if there is a way to have Excel automatically copy to the next month's sheet the rows of patients that are still in the system (have no date in the final stage column).

Since my original post, I have been learning Advanced Filters and that seemed to almost work, but I ran across a couple snags.  One was that when the patients were copied over to the next sheet, the Advanced Filter process overwrote the formulas in columns D, F, H, J, L, & N. So then I tried to not select those columns for the transfer but then got an 'list range' error.  Lastly, I was unclear if the Advanced Filter method could be saved and ran automatically at the first of the month.  Perhaps there is a different approach to this problem, but my macro skills are very limited.

Again, I apologize for the lengthy post & any help is greatly appreciated.

venkat1926 · Answer

take two examples one patient who has not straddled and one straddled and explain with reference to the cell addresses.

Big.Moe · Answer

Hello again and than you for the continued support,

First let me post a new link to an updated Excel file:
http://speedy.sh/CMQRG/Tracking-List2.xlsx

(I changed the dates to better represent the issue.)

So, let's take the top two patients as an example, which correspond to rows 4 & 5. The row 4 patient has finished all the stages hence having dates entered in all stage cells (C4, E4, G4, I4, K4, M4) with special attention to cell M4 which is the last stage.  Cell M4 is the really the cell that determines if the patient gets copied to the next month or not.  This patient (row 4) does have a date in M4 so he stays on this sheet and does not get copied to Sheet 'Feb 12'.

The second patient (row 5) has not finished all stages since it only has dates entered in the first two stage cells (C5 & E5).  Since M5 is missing the final date, this patient's information (row 5) should be copied to the next month's sheet ('Feb 12').

venkat1926 · Answer

why not just autofilter jan 12 sheet field column M and cirtieria blank
and copy filtered data to feb 12 sheet for which the macro is below


in each month end you have to change the sheet names in the macro and run the macro 

the file with feb12 filled can be downloaded from 

http://speedy.sh/Rdnaj/big-more-Tracking-List2.xlsm

one suggestion for permanent attention.  If you want to write a macro 
DO NOT make cosmetic changes to the sheets like coloring,merging, changing font  etc.  These cosmetic changes should be done only at the last stage that is just before presenting to your boss or users. Otherwise the writing macro becomes little more difficult and complicated.  Keep this in mind. 

The macro is in the module and is also repeated herer

if you want to retest run undo and then "test"

Sub test()
Dim filt As Range, r As Range
Worksheets("Jan 12").Activate
Set r = Range("A3").CurrentRegion
r.AutoFilter field:=Range("M1").Column, Criteria1:=""
Set filt = r.Offset(1, 0).Resize(r.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
'MsgBox filt.Address
filt.Copy
With Worksheets("Feb 12")
.Cells(Rows.Count, "A").End(xlUp).Offset(2, 0).PasteSpecial
End With
ActiveSheet.AutoFilterMode = False
Application.CutCopyMode = False
End Sub


Sub undo()
Dim llastrow As Long
Worksheets("Feb 12").Activate
llastrow = Cells(Rows.Count, "A").End(xlUp).Row
Range(Range("a5"), Cells(llastrow, "A")).EntireRow.Delete

End Sub

Big.Moe · Answer

Sorry it took me so long to reply.  Got busy there for a couple days and had to put this on the back burner.

Thank you so much for the Macro, works like a charm.  Nice work!!

Auto-copy of select rows to another worksheet

8 responses

Similar discussions

Subscribe To Our Newsletter!