How to get Row, Column from a reference within a cell

[showgun3]

Hi I have several sheets that I gets data from for sales and then copy them through referencing to other sheets. I would like to use VBA to get the remainder of the data automatically once I copy the first column.

Usually I would do =+'Sheet1'!A1 from a different sheet onto Sheet2 for example, from here on onwards I would like to use VBA to get the Sheet reference, row and column, then add to the column to get the next column and fill b3 automatically and to auto update all data in cells C3, D3, and E3. What I cant figure out is how to get the relevant sheet name, row and column numbers from A3. If there is a way through <acronym title="visual basic for applications">VBA</acronym>, help me please. I am not very good with VBA, just started learning.

Example below:

	A	B	C	D	E
1	10	20	30	40	50
2
3	=Sheet1!A1	=+B1
4
5

<tbody>
</tbody>

The result in A3 will be 10 and B3 will be 20, now I want a function which can analyze A3 autofill B3, C3, D3 and E3 with 30, 40 and 50 respectively.
​

 

[patel45]

I did not understand your goal but

nrow = range("A3").row
ncol = range("A3").column

 

[showgun3]

I want to get the sheet name from cell a3, and the row and column from the contents of cell a3. Can it be done.