I'm not sure I understood your request. This is my
interpretation.

As I read your post, you may have empty cells in the
data. I therefore structured the code to exclude empty
cells. Empty cells in the original data are deliberately
excluded in the copied data.

The code assumes that the headings ID#, Name and
Supervisor are in Cells A1 to C1 respectively with the
data starting immediately below. Four copies of each data
set are created and copied to Column E. Change the range
references to suit. Test the code with empty cells in the
original data. Note that you will have to correct for
wordwrap.

Const Pwd As String = "monkey"

Sub TestZZZ()
Dim txt As String, i As Integer, ii As Integer
Dim Rng As Range, C As Range, Rw As Integer

'<<<<< Password entry
i = 0
Do Until txt = Pwd
txt = InputBox("Enter password to
continue . . . ", "Password Entry")
If txt = "" Then
Exit Sub
ElseIf txt < Pwd Then
i = i + 1
If i = 3 Then
MsgBox "Access denied !!! " & _
"Contact your administrator for access to this
feature. ", _
vbInformation, "Access denied"
Exit Sub
End If
MsgBox "Password failed. Please try again. ",
vbExclamation, "Password entry"
End If
Loop

'<<<<< Transfer data
i = 0
Rw = Range("A65536").End(xlUp).Row
Set Rng = Range("A2:A" & Rw).SpecialCells
(xlCellTypeConstants)
For Each C In Rng
For ii = 1 To 4
Range(Cells(i + ii, 5), Cells(i + ii, 7)) = Range
(C, C.Offset(, 2)).Value
Next
i = i + 4
Next

End Sub

Regards,
Greg


-----Original Message-----
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 records
each to a different location in the same sheet, i.e.
after running the
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 paste
them accordingly without telling it what all to paste,
i.e. lets say if
i added "name 5" in the list and also made some changes
in "name2" it
would copy all the data into the desired result!!!
(without changing the
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 someone
with the rights can run the macro!!!

Could someone help me with this?

Thanks
Paritosh Mehta


---
Message posted from http://www.ExcelForum.com/

.