I have inherited a set of Excel files from my predecessor. The files consist of a single form with many sheets with sections that have been completed by other users. The input from users consists of data entered into cells and text boxes. There is a lot of data and different sheets with text boxes that I would like to summarise into a format I can work with easily.
I am not very familiar with macros in Excel, but I fudged together a macro that I can run on an empty, open Excel doc, that:
- asks for which file I want to open
- gets the 'Data' sheet and copies and pastes it into open active workbook
- closes the file I grabbed the data from
This covers all of the data that was entered into the cells.
I also want to populate some of the cells in the new workbook with the text from the text boxes. So in the end I have a summary of all of the data on a single sheet for each user. However, I am having issues finding any solutions online for how to gather the information from a text box successfully.
I have tried solutions based on suggestions from this thread, but I receive the error Run-time error '438': Object doesn't support this property or method : VBA/Macro code to get the value of textbox
Sub ffs()
Dim wbkS As Workbook
Dim wshS As Worksheet
Dim wshT As Worksheet
Dim strFile As String
With Application.FileDialog(1) ' msoFileDialogOpen
.Filters.Clear
.Filters.Add "Excel workbooks", "*.xls;*.xlsx;*.xlsb;*.xlsm"
.InitialFileName = "D:\Macro testing area"
If .Show Then
strFile = .SelectedItems(1)
Else
MsgBox "No file selected", vbCritical
Exit Sub
End If
End With
Application.ScreenUpdating = False
Set wshT = ActiveSheet
Set wbkS = Workbooks.Open(Filename:=strFile, ReadOnly:=True)
Set wshS = wbkS.Worksheets("Data")
Set wshS1 = wbkS.Worksheets("Section 1")
wshT.Range("A1:L88").Value = wshS.Range("A1:L88").Value
' Optional: close source workbook
wbkS.Close SaveChanges:=False
Application.ScreenUpdating = True
End Sub
Ideally I was hoping to add some additional code underneath this line of code, before the file is closed:
wshT.Range("A1:L88").Value = wshS.Range("A1:L88").Value
I would like to add the text box values from wbkS.Worksheets("Section 1") into specified cells. But my attempts have been unsuccessful, such as:
wshT.Range("J3").Value = wshS1.TextBox1_1.Value
I assume I am making a logical flaw in how Macros work, but I am having trouble finding a solution. Any advice would be greatly appreciated.