capture the text of a vba tag

Question

capture the text of a vba tag

Navigation

#1 by (1 votes)

0

I have a query, it is about a macro that I need to formulate, in it I need to gather the information of several sheets of a book in a single final book, in this I have no problem, my problem is that each sheet has a label with text, which I must capture and place in column A before pasting the info on the next page.

Sub hojas()

Dim i As Integer


    Sheets("WSP_Sheet44").Select
    Sheets(Array("WSP_Sheet44", "WSP_Sheet45", "WSP_Sheet46", "WSP_Sheet47", _
        "WSP_Sheet48", "WSP_Sheet49", "WSP_Sheet50", "WSP_Sheet51", "WSP_Sheet52", _
        "WSP_Sheet53", "WSP_Sheet54", "WSP_Sheet55", "WSP_Sheet56", "WSP_Sheet57", _
        "WSP_Sheet58", "WSP_Sheet59", "WSP_Sheet60", "WSP_Sheet61", "WSP_Sheet62", _
        "WSP_Sheet63", "WSP_Sheet64", "WSP_Sheet65", "WSP_Sheet66", "WSP_Sheet67", _
        "WSP_Sheet68")).Select
    Sheets("WSP_Sheet44").Activate
    Sheets(Array("WSP_Sheet69", "WSP_Sheet70", "WSP_Sheet71", "WSP_Sheet72", _
        "WSP_Sheet73", "WSP_Sheet74", "WSP_Sheet75", "WSP_Sheet76", "WSP_Sheet77", _
        "WSP_Sheet78", "WSP_Sheet79", "WSP_Sheet80", "WSP_Sheet81", "WSP_Sheet82", _
        "WSP_Sheet83", "WSP_Sheet84", "WSP_Sheet85", "WSP_Sheet86", "WSP_Sheet87", _
        "WSP_Sheet88", "WSP_Sheet89", "WSP_Sheet90", "WSP_Sheet91", "WSP_Sheet92", _
        "WSP_Sheet93")).Select Replace:=False
    Sheets(Array("WSP_Sheet94", "WSP_Sheet95", "WSP_Sheet96", "WSP_Sheet97", _
        "WSP_Sheet98", "WSP_Sheet99", "WSP_Sheet100", "WSP_Sheet101", "WSP_Sheet102", _
        "WSP_Sheet103", "WSP_Sheet104", "WSP_Sheet105", "WSP_Sheet106", "WSP_Sheet107", _
        "WSP_Sheet108", "WSP_Sheet109", "WSP_Sheet110", "WSP_Sheet111", "WSP_Sheet112", _
        "WSP_Sheet113", "WSP_Sheet114", "WSP_Sheet115", "WSP_Sheet116", "WSP_Sheet117", _
        "WSP_Sheet118")).Select Replace:=False
    Sheets(Array("WSP_Sheet119", "WSP_Sheet120", "WSP_Sheet121", "WSP_Sheet122", _
        "WSP_Sheet123", "WSP_Sheet124", "WSP_Sheet125", "WSP_Sheet126", "WSP_Sheet127", _
        "WSP_Sheet128", "WSP_Sheet129")).Select Replace:=False

            Columns("A:B").Select
    Range("B1").Activate
    Selection.Insert Shift:=xlToRight


>     For i = 44 To 129
>     Worksheet("WPS_Sheet" & i).Cells(1, 1).FormulaR1C1 = TextBox.Text
>     
>     Next


End Sub

vba macros excel

asked by INVEST_EXCEL 06.06.2018 в 17:30

source

1 answer

delete array repeated elements Data Grouping

score 1 · Accepted Answer

Ok, I think I understand what you say.

Inside your FOR loop, delete the part that says:

Worksheet("WPS_Sheet" & i).Cells(1, 1).FormulaR1C1 = TextBox.Text

and try this:

Worksheets("WPS_Sheet" & i).Cells(1, 1).FormulaR1C1 = Sheets("WPS_Sheet" & i).OLEObjects("TextBox 1").Object.Text

UPDATE: After viewing the user's file, it was found that the control was neither a textbox nor a label, but a form (shape). In addition, all the sheets start with the initials WSP and not WPS , producing the Error 9 subíndice fuera de intervalo . Because it was calling a sheet that does not exist.

For i = 44 To 129 Step 1
    ThisWorkbook.Worksheets("WSP_Sheet" & i).Cells(1, 1).FormulaR1C1 = ThisWorkbook.Worksheets("WSP_Sheet" & i).Shapes("TextBox 1").TextFrame.Characters.Caption
Next i

Checked on my computer and executed without errors:)