Excel Application.Input full address-CodePudding

I am trying to write a macro in Excel where I could format sheets dynamically. 1 macro, without a hardcoded sheet's name.

I don't know how to extract the entire value of the Application.Selection if the macro points to another sheet (and I need to do it on another sheet)

Sub GetRangeToModify()

    Dim RangeOfA1, RangeOfA2 As range
    
    Set RangeOfA1 = Application.Selection
    Set RangeOfA2 = Application.InputBox("Select the loaded CSV's sheet' A1 field", "Select A1 cell", RangeOfA1, Type:=8)
    MsgBox RangeOfA2.Address
End Sub

With this snippet you can get the location of the cell, but the sheet's name is not visible ( e.g. $K$16; not Sheet1!$K$16)

The plan is: get the full string from the selection (e.g: Sheet2!$L$19), activate another sheet with the extracted sheet's name, and do stuff there (insert new columns etc.)

Is there a way to do it properly?

CodePudding user response：

Try:

Dim RangeOfA1 as Range, RangeOfA2 As range 'must assign explicit data type for each declaration; otherwise, data type is Variant by default.

Dim strWS as String 'worksheet name 

Set RangeOfA1 = Application.Selection
Set RangeOfA2 = Application.InputBox("Select the loaded CSV's sheet' A1 field", "Select A1 cell", RangeOfA1, Type:=8)
 strWS = RangeOfA2.Worksheet.Name
 MsgBox "'" & strWS & "'!" & RangeOfA2.Address