Enter range from dialog box into formula in macro?

[PhilW_34]

Hello,

I have some repetitive work I would like to create a macro for. Here is a little background. I have sales data which is vertical on one tab. I am using a transpose formula to enter this vertical data into multiple sheets. These sheets then update whenever new data is entered in the sales tab. For each tab, I am selecting 4 or 5 horizontal cells, typing in =Transpose and then clicking the sales tab, selecting the 4 or 5 vertical cells I want to reference, hitting F4 to create absolute references, and then completing the array formula with the normal Shift-Control Enter. This is so tedious.

I've tried writing some code which I can run to display a dialog box asking me to select my range. I got this part down. The problem I am having is I can't figure out how to get this selected range to be entered into a transpose formula. I can't create a named range for other reasons.

I need just a bit of code that enters the range I just selected into my formula.

Here is what I've come up with.

Sub Macro3()

    Dim MySelection As Range
    Set MySelection = Application.InputBox(prompt:="Select a range of cells.", Type:=8)
    
    'I need to get rid of the line below.
    ActiveWorkbook.Names.Add Name:="Tpose", RefersTo:=MySelection
    
    'This works. However, I can't use a named range because I have to copy this range below
    'my data and do a find and replace.
    Selection.FormulaArray = "=TRANSPOSE(Tpose)"
    
    'This is an example of what I need. This is a range I select when prompted.
    'Selection.FormulaArray = "=TRANSPOSE('Linked Page'!$K$557:$K$561)"
    
End Sub

Thanks.

Phil

 

[PhilW_34]

Hello,

I figured it out and wanted to post my solution in case anyone else runs into this. That said, if anyone knows of a better solution, I'm always open to learning more. Thank you.

Sub Macro3()

    Dim MySelection As Range
    Set MySelection = Application.InputBox(prompt:="Select a range of cells.", Type:=8)
    Selection.FormulaArray = "=TRANSPOSE('Linked Page'!" & MySelection.Address & ")"
    
End Sub

Phil