I'm trying to figure out a way to merge the data generated at three different
versions of the same workbook (running on three different workstations) into a single workbook, but I'm running into trouble,
apparently because the data in each workbook is derived from formulas, rather than being plain, manually entered numbers. An
example of the data entry workbook (used for tallying statistics) and the current consolidating workbook are attached.
The workbook "Copy of Desk Tally Robot" tabulates statistics on the worksheet "Results Sheet"
based on control button clicks on the worksheet "Input Sheet." The "Save data to Shared Drive" button on
"Input Sheet" makes a copy of "Results Sheet" and saves that copy to a folder on a network drive -- the
folder and file name derived from NOW() in mmmm and mmmm, dd, yyyy formats respectively, with unique prefixes for each
workstation. The code for the save function, since I think it might have something to do with my trouble, is:
Dim InputDate As Variant
Dim FileType As String
Dim myFileName As String
Dim myFolder As String
Dim myMonth As String
FileType = ".xlsx"
myFileName = "Service Desk 2 Report " & Format(Now(), "mmmm dd yyyy")
myMonth = Format(Now(), "mmmm")
myFolder = "J:Library StatsService desk" & myMonth
If Dir(myFolder & "" & myFileName & FileType) <> "" Then Kill (myFolder &
"" & myFileName & FileType)
ActiveWorkbook.SaveAs myFolder & "" & myFileName & FileType
The "CONSOLIDATOR" workbook also uses a control button which prompts for the workbooks to be merged (this part
works) and then places each in its own worksheet (ideally I'd like it to merge the data into a single worksheet
automatically, but getting the three worksheets and consolidating them via the Data tab is acceptable for now).
Unfortunately, instead of giving me the data from the three worksheets, I get three new worksheets, each with data from the
same workbook. The code for the Consolidator is:
Dim x As Integer
On Error GoTo ErrHandler
Application.ScreenUpdating = False
FilesToOpen = Application.GetOpenFilename _
(FileFilter:="Microsoft Excel Files (*.xls*), *.xls*", _
MultiSelect:=True, Title:="Files to Merge")
If TypeName(FilesToOpen) = "Boolean" Then
MsgBox "No Files were selected"
x = 1
While x <= UBound(FilesToOpen)
Sheets().Move After:=ThisWorkbook.Sheets _
x = x + 1
Application.ScreenUpdating = True
I notice that when the Results Sheets are saved, individual cells are displaying the values at their last save, but if you
click into them, you get a formula referring back to the Input Sheet of the robot on the workstation where data was
originally input. Is there a way to either save the Results Sheets as plain values, without referring back, or consolidate
them based on the display values, and not the formulae? Any advice would be greatly appreciated, and if I've been unclear or
left out any useful information, please to let me know.