Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 542
  • Last Modified:

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

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
mnoisette
Asked:
mnoisette
  • 7
  • 4
1 Solution
 
redmondbCommented:
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
 
mnoisetteAuthor Commented:
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
 
redmondbCommented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
mnoisetteAuthor Commented:
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
 
redmondbCommented:
Perfect, thanks, Mo!

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

Thanks,
Brian.
0
 
mnoisetteAuthor Commented:
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
 
redmondbCommented:
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
 
redmondbCommented:
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
 
mnoisetteAuthor Commented:
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
 
redmondbCommented:
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
 
redmondbCommented:
Thanks, Mo.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 7
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now