VBA for copy and paste (Excel 2000)

[cindypositano]

I am trying to copy and paste down the spreadsheet. The customer name is only on every third row or so. I need the VBA code that will copy data from the first occupied cell in Column A and paste down to the next occupied cell. I need this copy command repeated for every new customer that occupies Column A. Help! Thank you!

[MichaelRead]

Can you post a simplified example of what you are trying to do?

[sdckapr]

If I understand correctly, I think this will do what you want. Copy code to VB.
Select the range of interest and run the macro
It will fill the blank areas of the region with the value of the cell above it

So if you have
a
(blank)
b
(Blank)
(Blank)
c
(Blank)
(Blank)
d

You will get
a
a
b
b
b
c
c
c
d

Steve

Sub FillBlanksFromAbove()
Dim temp As String
rng = Selection.Address

ActiveCell.Range("A2").FormulaR1C1 = "=+R[-1]C"
ActiveCell.Range("a2").Copy
Range(rng).SpecialCells(xlCellTypeBlanks).Select
ActiveSheet.Paste
Range(rng).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues
End Sub

[MichaelRead]

Hi Cindy,

As I mentioned in my previous post, would like to see a stripped down version of your workbook because...

...invariably, cells to the right of column A probably contain information relating to the customer names in column A
...you mention "new customers" in your original post...are these unique customers listed already in column A, or are they "new" customers related to the information in columns B, C, D, etc i.e. a new customer for the next order, etc?
...your statement "referring to every three rows or so" indicates that there is data to the right of column A, therefore what happens to the data when you paste a "new" or existing customer into column A?

Without a stripped down example with more specifics, I am not willing to make code based on assumptions.

[LegareColeman]

See if the routine below will do what you want:

<pre>Sub FillBlanksFromAbove()
Dim I As Long
Dim oLast As Range, oBlank As Range
Set oLast = ActiveSheet.Range("A65536").End(xlUp)
For I = 0 To oLast.Row - 1
If ActiveSheet.Range("A1").Offset(I, 0).Value <> "" Then Exit For
Next I
Set oBlank = ActiveSheet.Range("A" & I + 2 & ":" & oLast.Address).SpecialCells(xlCellTypeBlanks)
oBlank.FormulaR1C1 = "=+R[-1]C"
End Sub
</pre>

[LegareColeman]

Yes, now that I see your workbook I see where a couple of changes are necessary. Try the code below:

<pre>Sub FillBlanksFromAbove()
Dim I As Long, oLast As Long
Dim oBlank As Range
oLast = ActiveSheet.Range("H65536").End(xlUp).Row
For I = 1 To oLast - 1
If ActiveSheet.Range("A1").Offset(I, 0).Value <> "" Then Exit For
Next I
Set oBlank = ActiveSheet.Range("A" & I + 2 & ":A" & oLast).SpecialCells(xlCellTypeBlanks)
oBlank.FormulaR1C1 = "=+R[-1]C"
End Sub
</pre>

[cindypositano]

Post deleted by cindypositano

[LegareColeman]

What was the error?

[cindypositano]

Post deleted by cindypositano

[LegareColeman]

I copied that line of code from your message and pasted it back into the macro that I tested, and I did not get an error. It is possible that some other line is causing the problem. Could you upload the workbook with the code?

[cindypositano]

Post deleted by cindypositano

[LegareColeman]

It looks like the Less Than sign in that line was converted to the HTML pseudo code when you copied the code from the message. I have attached the workbook you sent with that corrected.

[cindypositano]

That was it! I need new glasses! Thank you. This project is now complete and ready for the next phase. I really have appreciated your help. Thanks to you I can keep my job!

[cindypositano]

Mike, I made my deadline for today on this phase of the project thanks to you. I appreciated your help.

[MichaelRead]

Glad I could be of help.