Posts by ArunPeswani

I just found the way to do it here.

Sorry for posting external link in this post. I am sure it will help others as well.

Thanks a lot for your replies royUK!

Hi rouUK.

Thanks for the quick revert.

It still shows msgbox result as "Sheet1!$A$1". It does not show workbook name and shows only first cell of selection. It is very helpfull though. Can we add workbook name (file name) and if it can show full range selected?

Thanks in advance for your quick help.

Quote from jolivanes

You cannot have a macro enabled workbook named "Destination.xlsx"

It has to be either the older Excel version(.xls) or the current extension .xlsm

Thanks for informative reply.

Hi royUK,

Thanks for your reply.

You are right about description of xlsx, xls, xlxm extentions.

However, your prescribed solution did not work as desired. I need the user to select some cells in a worksheet other than the one where macro nis recorded. Hence, the inputbox will show the range address as [Source.xlsx]SheetA!$A$1:$C$10 which I want to copy or store in a variable, while your code displays msgbox with simple address as $A$1:$C$10 which I was anyways getting from my existing code.

Do it like this, save macro in workbook1, run it, inputbox will appear in screen, switch to workbook2 and select some cells. It will show you the address of selected cells as [Source.xlsx]SheetA!$A$1:$C$10 and not as $A$1:$C$10.

I have coded a small vba Macro in the workbook named Destination.xlsx, and it is as follows:

Sub GetDataArea()
    Dim rng As Range
    Set rng = Application.InputBox("Select the cells.", Type:=8)
End Sub

I have selected InputBox Type:=8 because I want users to select some cells and then I want to copy the cell reference or address of those selected cells. The problem here is that the Macro is coded in Destination.xlsx, I want the user to select cells in Source.xlsx (it can be any workbook of users' choice), and copy the cell reference or cell address of Source.xlsx. It looks like the screenshot attached here >InputBox

Basically, I want to copy the text [Source.xlsx]SheetA!$A$1:$C$10 from my Inputbox. I am not sure how do I get this. I tried rng.address but it returns only $A$1:$C$10 While need complete reference as [Source.xlsx]SheetA!$A$1:$C$10

Pls help...