I want to capture data within a worksheet from 1 sheet to another but the position of the data is in the incremental of 58 cells. For example:-

=+'Prospect Details'!C502 (1st cell position)

=+'Prospect Details'!C560 (2nd cell position)

=+'Prospect Details'!C618 (3rd cell position)I am currently changing the position of the cell (ie., C502 to C560) manually by typing the new cell position over the initial one.

Is it possible to use macro, if yes, how? Thanks.

First, you don't need the + sign in an Excel formula. Second, you could do it without a macro:

In a "spare" column e.g. G, try this:

Enter 502 in G1

Enter =G1+58 in G2

Drag it down as far as you need.In the cell where you want your first formula enter:

=INDIRECT("'Prospect Details'!C"&G1)

Make sure you include the single quotes around the Sheet name.

Drag this down as far as you need.

Finally, if you want a macro, try this:

Assumptions:

- Your first formula goes in A1

- You need 10 formulas, from C502 to C1024Sub Formula58() For nxt58 = 502 To 1024 Step 58 myRw = myRw + 1 Range("A" & myRw).Formula = _ "='Prospect Details'!C" & nxt58 Next End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Thank you so much! I have 2 tiny details to check about the macro solution you so kindly provided.

1) If my 1st formula goes in C3 -- how do I change the range in the macro?

2) If my formula covers 1000 cells, ie., 502 to 58444, do I replace your original as follows:-

For nxt58 = 502 To 58444 Step 58Your macro solution is very much appreciated -- I am forever grateful!!

Try this: Sub Formula58() 'Intitalize myRw variable myRw = 2 'Loop through range For nxt58 = 502 To 58444 Step 58 'Increment myRw variable myRw = myRw + 1 'Place formula, starting C3 Range("C" & myRw).Formula = _ "='Prospect Details'!C" & nxt58 Next End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

THANK YOU!! I tried it and IT WORK!!!!

It would have taken me forever to figure the solution out.

I appreciate what you are doing alot!! Again . . . THANKS!! ;O)

One last question please. . . Can I modify this macro for hpyerlink with the same scenario, 1,000 cell with 58 cells increment? Please show me how, if it is possible. Thank you, thank you!!

Sub Formula58()

'Intitalize myRw variable

myRw = 2

'Loop through range

For nxt58 = 502 To 58444 Step 58

'Increment myRw variable

myRw = myRw + 1

'Place formula, starting C3

Range("C" & myRw).Formula = _

"='Prospect Details'!C" & nxt58

Next

End Sub

re: " modify this macro for hpyerlink with the same scenario"I don't know what you mean by "

hyperlink with the same scenario".

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

I want to link 1000 cells to 1000 record. Sheet 1, Cell A3 - A1002 hyperlink to sheet 2 cell C5 to C57947

For example:

1st cell position sheet 1, A3 to sheet 2 cell C5

2nd cell position sheet 1, A4 to sheet 2 cell C63 (an increment of 58 cells)

3rd cell position sheet 1, A5 to sheet 2 cell 121 (an increment of 58 cells)Can I use macro to do this? If so how?

Thank you.

Ask Your Question

Weekly Poll

Do you think Microsoft's new Surface Go will be a hit?

Discuss in The Lounge

Poll History