Automatic data update macro running absolutely fine, just not pasting

[Siddie]

The code below runs/loops just fine and pastes the cells(7,9) to the other sheet. It also pops up and stops the msg box on meeting a blank cell. However, other than that first cell there is no other cell on the target sheet that is being populated. I've tried assigning values directly through another string as well, no-fun!, the only data it transfers is the first cell.

Also, I would be glad to further shorten this code ...

Any help will be cordially appreciated ...

Private Sub CommandButton2_Click()


Dim L As Integer
Dim aData As String
'Dim bData As String
Dim myCell As Range
L = 7
'activate the control panel
Worksheets("Review & Update").Activate


'Assigning the data and cell location to strings
aData = Cells(L, 6).Value
'bData = Cells(L, 9).Value
Do
' Action
Cells(L, 9).Select
If Cells(L, 9).Value = "" Then
MsgBox "Opps! we met a blank cell"
Exit Do
Else
Cells(L, 9).cOpy
Set myCell = Sheets("attendance").Range(aData)
' Goto proper sheet/address
Worksheets("Attendance").Activate
myCell.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
'myCell.Value = bData
L = L + 1
Worksheets("Review & Update").Activate
End If
Loop
End Sub

 

[footoo]

This needs to be inside the Do...Loop :

aData = Cells(L, 6).Value
'bData = Cells(L, 9).Value

 

[footoo]

Also, I would be glad to further shorten this code ...

Does column 9 contain formulas or constants ?
Are the cell refs in column 6 sequential (e.g. D5,D6,D7,....) ?