Copy and reference a lot of data easy quick way???

[dracron]

right now I have in column A =worksheet2!$A$2 and column B =Worksheet2!$B$1 for the first 41 cells and then the next 41 cells I have =worksheet2!$A$2 and column B =Worksheet2!$C$1. and so on. Problem is I would have to type this over and over again for 100s of different cells.

Is there a quicker way I can do this in a macro?

 

[Joe4]

Place this formula in cell A1, and copy down for all rows. Every 41 rows, it will pull the next cell from column A on worksheet2.

=INDIRECT("worksheet2!$A$" & INT(((ROW()-1)/41)+1))

Just adjust the column letter reference to use this same formula for other columns.

 

[dracron]

Works great! exactly what I was looking for thank you!!
How can I make it start from A2?
Additionally, How could I adjust this so it will take data horizontally and paste vertically? I need to take data from B1:AO1 and copy that vertically in the 41 cells over and over again.

I love this site!

 

[Joe4]

How can I make it start from A2?

=INDIRECT("worksheet2!$A$" & INT(((ROW()-2)/41)+1))

Additionally, How could I adjust this so it will take data horizontally and paste vertically?

Maybe:
Copy -> Paste Special -> Transpose

 

[dracron]

I figured out the -2 right before seeing your message lol.
I was hoping to avoid using transpose and have it run automatically but its not that big of a deal. Appreciate your help my friend!

 

[Joe4]

I was hoping to avoid using transpose and have it run automatically but its not that big of a deal.

There may be a way of doing it using INDIRECT also, but it would be a bit more complex, since you would have to convert column letters to their equivalent numbers. I did't really try it, because I am unsure of what exactly is being copied to where. I would need you to describe the first few iterations of that.

You may be able to use Macro/VBA code to automate the redundancy of Copy -> Paste Special Transpose actions too.