Solved

Copy data from one Excel sheet to another based on criteria VBA

Posted on 2013-02-04
11
502 Views
Last Modified: 2013-03-12
I have a workbook with the months January through December.
At the end of each month, if the patient has not been released, I would like the line(s) of information to be copied to the following month.

Attached is a copy of a test workbook.
Hospital-Report-Test.xlsm
0
Comment
Question by:mnoisette
  • 7
  • 4
11 Comments
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
Hi,

A few questions, please...
(1) How do know which month to process - simply use the month previous to the current date?
(2) When processing the December sheet do we really loop back to the "January" sheet or will you add the year to the sheet name? (If the latter then we could simply copy the previous month's sheet to create the new one (and drop the released patients.)
(3) Do you want anything done to the Summary sheet.?

Thanks,
Brian.
0
 

Author Comment

by:mnoisette
Comment Utility
Brian,

Answers:

(1) If a patient does not have a release date entered, then, yes you can simply use the month previous to the current date...

(2) When December comes, you can just loop back to the January sheet. I think that would be easy for my boss.

(3) The Summary sheet only contains patients that have not been released. That one should be fine as is...

Thanks!
Maurice
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
Thanks, Maurice.

(1) If a patient does not have a release date entered, then, yes you can simply use the month previous to the current date...
I assume that whenever the macro is run it only process the data in one sheet. If that's correct then I need to know which sheet to use.

Thanks,
Brian.
0
 

Author Comment

by:mnoisette
Comment Utility
Thanks Brian,

Use only the sheet to the left of the current month sheet.
I think thats the answer you may be looking for...

Mo
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
Perfect, thanks, Mo!

Now one more - the formulas in column D is circular. What isw it supposed to be?

Thanks,
Brian.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:mnoisette
Comment Utility
Brian,

I didn't create that one...
But it pulls the info into the Summary sheet if the "Actual Release Date" is blank. But it only works for January.

The concept is that if the patient has not been released from any of the months (January through December) the Summary sheet shows a list off all the patients for all the months.

Hope it makes sense....
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
Mo,

Edit:
Sorry, we're talking at cross purposes - I'm concerned about the circular references in column D of the detail sheets, i.e January to December. By all means, just give me an English explanation of what should be in those cells.

Edit2:
I'm assuming that any row with data on it will have a non-blank value in column A.

Regards,
Brian.
0
 
LVL 26

Accepted Solution

by:
redmondb earned 500 total points
Comment Utility
Mo,

Please see my previous post.

Please see attached. You can run it by using the blue button on the Summary sheet. The code is...
Option Explicit
Option Base 0 'Default

Sub Roll_Forward()
Dim i             As Long
Dim xCount        As Long
Dim xResponse     As Long
Dim xFirst_Data   As Long
Dim xlast_Col     As Long
Dim xLast_Row_Old As Long
Dim xLast_Row_New As Long
Dim xThis_Month   As String
Dim xSheet_Old    As Worksheet
Dim xSheet_New    As Worksheet

xFirst_Data = 5
xlast_Col = 10

Set xSheet_Old = ThisWorkbook.Sheets(Array("December", "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November")(Format(Now(), "m") - 1))
Set xSheet_New = ThisWorkbook.Sheets(Array("January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December")(Format(Now(), "m") - 1))
xResponse = MsgBox("About to move undischarged patients from " & xSheet_Old.Name & " to " & xSheet_New.Name & Chr(10) & "'OK' to continue, 'Cancel' to end run.", vbOKCancel, "Roll Forward")
If xResponse = 2 Then
    MsgBox ("Use chose to cancel.")
    Exit Sub
End If

' Find the sheets' last row...
xLast_Row_Old = Get_Last_Row(xSheet_Old, xFirst_Data)
If xLast_Row_Old = 0 Then Exit Sub

xLast_Row_New = Get_Last_Row(xSheet_New, xFirst_Data)
If xLast_Row_New = 0 Then Exit Sub

' Do it...
For i = xFirst_Data To xLast_Row_Old
    If xSheet_Old.Cells(i, 9) = "" And xSheet_Old.Cells(i, 1) <> "" Then
        xLast_Row_New = xLast_Row_New + 1
        xCount = xCount + 1
        xSheet_Old.Range(xSheet_Old.Cells(i, 1), xSheet_Old.Cells(i, xlast_Col)).Copy Destination:=xSheet_New.Range("A" & xLast_Row_New)
    End If
Next

MsgBox ("Run complete - " & xCount & IIf(xCount = 1, " entry", " entries") & " copied.")
    
End Sub

Function Get_Last_Row(xSheet As Worksheet, xFirst_Data As Long) As Long
Dim i As Long

With xSheet
    
    Get_Last_Row = .UsedRange.Cells(1, 1).Row + .UsedRange.Rows.Count - 1
    If Get_Last_Row < xFirst_Data Then
        MsgBox ("No data found in " & .Name & " - run cancelled.")
        Get_Last_Row = 0
        Exit Function
    End If
    For i = Get_Last_Row To xFirst_Data - 1 Step -1
        If .Cells(i, 1) <> "" Then Exit For
    Next
    If i < xFirst_Data Then
        MsgBox ("No data found in " & .Name & " - run cancelled.")
        Get_Last_Row = 0
        Exit Function
    End If
    
    Get_Last_Row = i
    
End With

End Function

Open in new window

Regards,
Brian.Hospital-Report-Test---V2.xlsm
0
 

Author Comment

by:mnoisette
Comment Utility
Brian,

Please keep in mind that usually when people ask for reports, they don't even know what they are asking for...LOL

The person asking for this report (not me) wants the Summary page to show patients that are still in the hospital and not released yet.
For example, if a ptient is admitted on Feb 25th, he may still be in the hospital come March 1st. The Summary sheet would show all these patients.

Please disregard that formula in column D. The person that started this workbook wasn't sure what to do. Delete it if you need to.

And, yes.... Column A would have a non-blank value for any row with data.

Thanks Brian
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
Thanks, Mo.

The person asking for this report (not me) wants the Summary page to show patients that are still in the hospital and not released yet.
And that's what it is doing - and for all months, not just January.  The reason you thought the other months weren't working is because they are way down the summary sheet. - click on the "UPDATE FILTER" button to make the report look sensible!

Please disregard that formula in column D. The person that started this workbook wasn't sure what to do. Delete it if you need to.
Done. And I've also updated each sheet's Heading to 2013, please see attached.

Regards,
Brian.
Hospital-Report-Test---V3.xlsm
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
Thanks, Mo.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now