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")
Selection.Copy
Set Exampl_Annual_Summary_Workbook = ActiveWorkbook
Worksheets("Sheet1").Select
Worksheets("sheet1").Range("B29").Select
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
Exampl_Annual_Summary_Workbook.Save
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
