Fill Macro

[linesy]

Hi,

I have a pivot table that I have copied, paste special value into another file so that I can sort a specific way. Column A has the Vendor, Column B the business unit. There are multiple business unit but the pivot shows the vendor once and then each of the business units. I need a macro to auto-fill the vendor inforomation in the left hand column (column A) for each line there is a business unit. Does anyone have any ideas??

Thanks.
Susan

 

[Weaver]

if you select the blank cells in column A and insert the formula

=r[-1]c

Sub test()
With ActiveSheet.Columns(1)
  .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=r[-1]c"
End With
End Sub

 

[JackDanIce]

If your vendor is in cell A1, try:

Sub FillDown ()
Range("A1:A" & Range("B" & Rows.Count).End(xlUp).Row).FillDown
End Sub

 

[Weaver]

A slightly smarter version

Sub fill2()
    With Range("A2", Cells(Rows.Count, "B").End(xlUp).Offset(, -1))
        .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=r[-1]c"
        .Value = .Value
    End With
End Sub

 

[linesy]

Weaver, worked perfectly. Thanks so much guys....you're great !!!

 

[Ann99]

Everyone, thanks, this is what I was looking for today!

I need a little more help with it.

My pivot table has 7 columns in the row area and the one necessary in the data area. The first 3 columns have blank rows under a row with a value. Can I use a macro to fill in the blanks with the value above in all three columns at once?

I did copy and paste the pivot table data onto a new worksheet, ran a macro to fill in col A, then copied and insered the other columns into column A to use the macro. It seems it should be easier.

Also a problem, on my trial worksheet, the "fill2" macro worked the first time, then after that only filled in the first blank under the topmost value. So I tried the "test" macro. Luckily it worked in mutiple trials. What could have happened with the "fill2" macro?

Thanks, Ann

 

[Ann99]

Hi, this is Ann
I take back some of my last post in this thread. I went back to my trial page and now both the Fill2 and Test macros are working! Don't know why the Fill2 didn't work before. But don't think any more about it.

I just need a macro, if possible, to fill in blank rows with a value in a row above in column A and also in columns B, C, etc at the same time. Thanks!