THANK YOU!!!!!!
-----Original Message-----
One way:

Public Sub test1()
Dim cell As Range
On Error Resume Next
For Each cell In Columns("G").SpecialCells
(xlCellTypeBlanks)
With cell.Offset(0, 12) 'Column S
.Offset(-1, 0).Value = .Value
End With
Next cell
on Error GoTo 0
End Sub

Note that you almost never need to select/activate.
Working with the
range objects directly results in code that is smaller,
faster, and
in my opinion, easier to maintain.

The On Error Resume Next is necessary since SpecialCells
() will
throw an error if there are no blank cells in column G.

In article ,
"Bonnie" wrote:

Hello All
I hope someone can help me with this ASAP

I have a Sheet that in column G I want my VBA to look
down for a blank cell, when it finds a blank cell goto
column S (same row that the blank in G was found) and
do
a copy, go up one cell, paste special/value. Then go
back
G and continue on..etc. etc.

this is what I have so far
and it works, but only for one copy and paste in
column S
How can I define the "range" in S so that I do not
have
to use a cell ref. exp. "S3"

Any help would be good
Thank you!!

Sub CopyPaste()

Dim Counter
Dim i As Integer

Counter = 600
Range("g1").Select
ActiveCell.Select
For i = 1 To Counter
If ActiveCell = "" Then
Range("S3").Select
Application.CutCopyMode = False
Selection.Copy
ActiveCell.Offset(-1, 0).Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False

Else
ActiveCell.Offset(1, 0).Select
End If
Next i
End Sub

.