Transfer data from Daily Staffing Workbooks into a Running Annual Summary Workbook


  • I am trying to make my life easier. I just inherited a bunch of workbooks used for compiling data where all the data was entered manually from a daily workbook into an annual summary workbook. It seems to me that transferring this data manually is tedious and unnecessary. So what I would like to be able to do is transfer data from these daily workbooks (the data on these is entered by others manually, daily) into a summary workbook, via programming, so that I do not have to manually enter the data. This will make more sense by looking at the attached examples.
    In attached files have all details. 

    I have a code for this but <g class="gr_ gr_17 gr-alert gr_spell ContextualSpelling" data-gr-id="17" id="17">its</g> not work properly
    Here is code:

    Sub WriteCode()
    Dim month As String
    Dim UNIT As String
    Dim Total_Census As String
    Dim HRS_CARE As String
    Dim HRS_CARE_AVERAGE As String
    Dim Total_SS_HOURS As String
    Dim Total_RN_OT As Single
    Dim Total_LPN_OT As Single
    Dim Total_NA_OT As Single
    Dim vFile As Variant
    vFile = Application.GetOpenFilename("Excel-files,*.xls", _
        1, "Select One File To Open", , False)
    'if the user didn't select a file, exit sub
    If TypeName(vFile) = "Boolean" Then Exit Sub
    Workbooks.Open vFile
    Worksheets("TALLY SHEET").Select
    month = Range("$A1")
    Worksheets("TALLY SHEET").Select
    UNIT = Range("$H3")
    Worksheets("TALLY SHEET").Select
    Total_Census = Range("$H$4")
    Worksheets("TALLY SHEET").Select
    HRS_CARE = ("$Q3")
    Worksheets("TALLY SHEET").Select
    HRS_CARE = ("$Q4")
    Worksheets("TALLY SHEET").Select
    HRS_CARE = ("$Q5")
    Worksheets("TALLY SHEET").Select
    HRS_CARE_AVERAGE = ("$R5")
    Worksheets("TALLY SHEET").Select
    HRS_CARE_AVERAGE = ("$R3")
    Worksheets("TALLY SHEET").Select
    Total_SS_HOURS = ("$S3")
    Worksheets("TALLY SHEET").Select
    Total_RN_OT = ("$T3")
    Worksheets("TALLY SHEET").Select
    Total_LPN_OT = ("$U3")
    Worksheets("TALLY SHEET").Select
    Total_NA_OT = ("$V3")
    Set Exampl_Annual_Summary_Workbook = ActiveWorkbook
    RowCount = Worksheets("sheet1").Range("B29").CurrentRegion.Rows.Count
    With Worksheets("sheet1").Range("B29")
    .Offset(RowCount, 0) = month
    .Offset(RowCount, 2) = UNIT
    .Offset(RowCount, 3) = Total_Cencus
    .Offset(RowCount, 4) = HRS_CARE
    .Offset(RowCount, 5) = HRS_CARE
    .Offset(RowCount, 6) = HRS_CARE
    .Offset(RowCount, 7) = HRS_CARE_AVERAGE
    .Offset(RowCount, 8) = HRS_CARE_AVERAGE
    .Offset(RowCount, 9) = Total_SS_HOURS
    .Offset(RowCount, 10) = Total_RN_OT
    .Offset(RowCount, 11) = Total_LPN_OT
    .Offset(RowCount, 12) = Total_NA_OT
    End With
    End Sub

    excel <g class="gr_ gr_42 gr-alert gr_spell ContextualSpelling ins-del multiReplace" data-gr-id="42" id="42">Vba</g>, Macros, VBA

    Sunday, November 08, 2015 10:30 PM