Help in creating a Macro

[paritoshmehta]

Hi,

I have the following data:

ID# | Name | Supervisor
123 | Name1| SUP1
456 | Name2| SUP2
789 | Name3| SUP3
124 | Name4| SUP4
..................................
..................................



I want to create a Macro which would copy this data and paste 4 record
each to a different location in the same sheet, i.e. after running th
macro the data should look like:

ID# | Name | Supervisor
123 | Name1| SUP1
123 | Name1| SUP1
123 | Name1| SUP1
123 | Name1| SUP1
456 | Name2| SUP2
456 | Name2| SUP2
456 | Name2| SUP2
456 | Name2| SUP2
789 | Name3| SUP3
789 | Name3| SUP3
789 | Name3| SUP3
789 | Name3| SUP3
................


What I want is a macro which would copy the non empty cells and past
them accordingly without telling it what all to paste, i.e. lets say i
i added "name 5" in the list and also made some changes in "name2" i
would copy all the data into the desired result!!!(without changing th
other cells, i.e. just paste it to column a,b,c and thats it!)
I also wanted to have a password to run the macro, i.e. only someon
with the rights can run the macro!!!

Could someone help me with this

 

[Bernie Deitrick]

Paritosh,

How about a formula solution?

Assuming your table starts in cell A1, then in your first row of your other
table, enter
=A1
in the second row, enter
=OFFSET(A$1,INT((ROW(A2)+ROW(A$2))/4),0)
Copy this second formula down for as many rows as you need (and extra for
when you add more values), and then copy all your formula cells in that
first column for two more columns to the right to make your table.

HTH,
Bernie
MS Excel MVP

 

[paritoshmehta]

Hey,

This works pretty fine for me!!!

Thanks for the reply!!!
Paritos