I have anfile with 10 worksheets. Each worksheet has about 5 to 10 activex textboxes. How do I extract the texts from these textboxes such that they will all go into an 11th worksheet - arranged per worksheet? For example, column 1 of worksheet 11 will contain the texts from the textboxes from worksheet 1, and so forth. Thanks.
Reeko assume the summary sheet is named summary (location to place the data)
Sub copydata()
Dim sh As Worksheet, sh1 As Worksheet
Dim col As Long, rw As Long
Dim obj As OLEObject
Set sh1 = Worksheets("Summary")
col = 0
For Each sh In Worksheets
If sh.Name <> sh1.Name Then col = col + 1 rw = 0 For Each obj In sh.OLEObjects If TypeOf obj.Object Is MSForms.TextBox Then rw = rw + 1 sh1.Cells(rw, col) = obj.Object.Value End If Next End If
Next End Sub
That worked for me.
Advertisement