Macro to copy relative data range fr wrksheet

May 23, 2011 at 00:23:15
Specs: Windows 7
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.


See More: Macro to copy relative data range fr wrksheet

Report •

#1
May 23, 2011 at 04:42:50
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 C1024

Sub 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.


Report •

#2
May 23, 2011 at 07:07:15
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 58

Your macro solution is very much appreciated -- I am forever grateful!!


Report •

#3
May 23, 2011 at 08:00:39
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.


Report •

Related Solutions

#4
May 23, 2011 at 18:49:31
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)


Report •

#5
May 23, 2011 at 23:59:58
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


Report •

#6
May 24, 2011 at 02:46:45
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.


Report •

#7
May 24, 2011 at 21:46:45
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.


Report •

Ask Question