If VBA with copy

[crazybuckeyeguy]

Trying to write VBA that looks for blank cell in column A. if blank copy the cell directly and to column G above it and paste in the blank cells. Im a little stuck. here is what I have.

Sub Copyname()
Application.ScreenUpdating = False
For MY_ROWS = 1 To Range("A" & Rows.Count).End(xlUp).Row
If Range("A" & MY_ROWS).Value = "" Then
Range("A:G" & MY_ROWS).Value = Range("A:G" & MY_ROWS - 1).Value
End If
Next MY_ROWS
Application.ScreenUpdating = True
End Sub

 

[Michael M]

Try this
When working with a range you need to specify the row reference for "both ends" of that range !!

Sub Copyname()
Application.ScreenUpdating = False
For MY_ROWS = 1 To Range("A" & Rows.Count).End(xlUp).Row
If Range("A" & MY_ROWS).Value = "" Then
Range("A" & MY_ROWS & ":G" & MY_ROWS).Value = Range("A" & MY_ROWS & ":G" & MY_ROWS - 1).Value
End If
Next MY_ROWS
Application.ScreenUpdating = True
End Sub

 

[crazybuckeyeguy]

Try this
When working with a range you need to specify the row reference for "both ends" of that range !!

Sub Copyname()
Application.ScreenUpdating = False
For MY_ROWS = 1 To Range("A" & Rows.Count).End(xlUp).Row
If Range("A" & MY_ROWS).Value = "" Then
Range("A" & MY_ROWS & ":G" & MY_ROWS).Value = Range("A" & MY_ROWS & ":G" & MY_ROWS - 1).Value
End If
Next MY_ROWS
Application.ScreenUpdating = True
End Sub

Of Course! thank you for your help Michael.

 

[Michael M]

glad to help...

 

[Michael M]

I think theres a typo in there though...try this

Sub Copyname()
Application.ScreenUpdating = False
For MY_ROWS = 1 To Range("A" & Rows.Count).End(xlUp).Row
If Range("A" & MY_ROWS).Value = "" Then
Range("A" & MY_ROWS & ":G" & MY_ROWS).Value = Range("A" & MY_ROWS[color=red]-1 [/color]& ":G" & MY_ROWS - 1).Value
End If
Next MY_ROWS
Application.ScreenUpdating = True
End Sub

 

[Peter_SSs]

.. and really all the codes would be better to start at row 2, not row 1.
If row 1 always has something in column A, it is just a waste checking anyway
and if it is possible that A1 is blank then the current codes will error.

 

[crazybuckeyeguy]

I think theres a typo in there though...try this

Sub Copyname()
Application.ScreenUpdating = False
For MY_ROWS = 1 To Range("A" & Rows.Count).End(xlUp).Row
If Range("A" & MY_ROWS).Value = "" Then
Range("A" & MY_ROWS & ":G" & MY_ROWS).Value = Range("A" & MY_ROWS[COLOR=red]-1 [/COLOR]& ":G" & MY_ROWS - 1).Value
End If
Next MY_ROWS
Application.ScreenUpdating = True
End Sub

I had to go and make this more complicated. I need to change it to something like this. But I need it to paste as;
PasteSpecial Paste:=xlPasteValues.
when it just mirrors the values it is changing the formatting from general to time and I need it to stay as general and this was the only solution I found.

Sub Copyname()
Application.ScreenUpdating = False
For MY_ROWS = 1 To Range("A" & Rows.Count).End(xlUp).Row
If Range("A" & MY_ROWS).Value = "" Then
Range("A" & MY_ROWS).Value = Range("A" & MY_ROWS - 1).Value
Range("c" & MY_ROWS).Value = Range("h" & MY_ROWS).Value
Range("e" & MY_ROWS).Value = Range("j" & MY_ROWS).Value
Range("g" & MY_ROWS).Value = Range("l" & MY_ROWS).Value
End If
Next MY_ROWS
Application.ScreenUpdating = True
End Sub

 

[Fluff]

This should preserve the format

Sub Copyname()
Dim My_ROWS As Long
Application.ScreenUpdating = False
For My_ROWS = 2 To Range("A" & Rows.Count).End(xlUp).Row
If Range("A" & My_ROWS).Value = "" Then
Range("A" & My_ROWS - 1).Copy Range("A" & My_ROWS)
Range("h" & My_ROWS).Copy Range("c" & My_ROWS)
Range("j" & My_ROWS).Copy Range("e" & My_ROWS)
Range("l" & My_ROWS).Copy Range("g" & My_ROWS)
End If
Next My_ROWS
Application.ScreenUpdating = True
End Sub