Macro - copy and paste value

[Howard]

When i downloaded the detailed suppliers invoices from my accounting
software, the layout is:
Supplier name in column B (only shown once on top of all the invoices
outstanding)
Invoice detail in column C, D, E & F (details shown immediately after the
supplier name in Column B)

The list is for all suppliers.

Is there any macro can be used to copy the supplier name and paste down
wards so that i can use sumif function to get detailed aging report based on
the invoice date in column C.

The macro should be able to recognised different suppliers name i.e. row 23
is Supplier A name and its related details run from row 24 to row 56 and
supplier B name at row 57 and details run from row 58 to row 80.

This macro should be able to copy supplier A from row 23 in column A and
paste from row 24 to 56 in column A. Then copy supplier B name in row 57 in
column A, paste from row 58 to row 80.

Hopefully this is not too confusing!

 

[Jacob Skaria]

Howard, you have mentioned that 'Supplier name in column B '. Towards the end
of your description you have again mentioned that "supplier A from row 23 in
column A"

I am assuming you have your supplier name in ColumnB. You really dont need a
macro to do this, However both solutions are given.

Sub MyMacro()
Dim lngRow As Long
For lngRow = 2 To Cells(Rows.Count, "C").End(xlUp).Row
If Trim(Range("B" & lngRow)) = "" Then _
Range("B" & lngRow) = Range("B" & lngRow - 1)
Next
End Sub



1. Select the data range in ColA (say B1:B100).
2. Press F5. From 'Goto window'>Special> from Options select 'Blanks' and
hit OK
3. This will select all blanks.
4. Now press = (equal sign)
5. Then press Up Arrow to reference the cell just above
6. Now press Ctrl and Enter key together


If this post helps click Yes

 

[Howard]

Dear Jacob,

This is very useful and on the GOTO Special function, is there any resource
that i can read up to understand the logic on how excel work on this.

Thanks A loT!

 

[Jacob Skaria]

Thanks for the feedback. I don't have any articles/link as such to refer
other than googling. May be search for "Excel GoTo Dialog".Cheers