VBA code need help on CopyPaste

  • Thread starter Thread starter alienscript
  • Start date Start date
A

alienscript

I have a list of PartNumber ranging from B2:B6880 and I am vlooking them
up to other worksheets for values of Safety-Stock, Qty-on-hand, PO-Qty,
etc..

For example, I insert column N to vlookup for the Safety-Stock values.
Double-clicking the black cross-sign that appears on the bottom-right
of cell N2 Vlookup formula doesn't copy the N2 formula to N6880 because
there are some blank cells in column M and column O.

I am hoping someone can help me with a code which can allow to copy
cell N2 formula to the last cell in column N where the whole row is not
totally blank, ie. at least has a cell in the row populated with
value.
Eg, if this row as mentioned above is row 6880, then row 6881 onwards
will be totally blank.


Appreciate so much if someone can help with with a code to do this.
Thanks so much in advance!
 
The simple solution would be to drag the formula down. However, if you want
a macro solution:

Sub CopyToEnd() 'untested
Dim LastRow As Long
LastRow = Cells(Rows.Count, 2).End(xlUp).Row
Range("N2").Copy Range("N3:N" & LastRow)
End Sub
 
Hi Vasant,

Thanks for your help.

What if in stead of selecting this:

Range("N2").Copy Range("N3:N" & LastRow)

I want to randomly select any cells in row 2 to CopyToEnd, how can I
use the Selection in ActiveSheet to do this job ?

Thanks in advance for help.
 
Hi alienscript


Code:
--------------------


Dim col As Long
col = ActiveCell.Column
Range(Cells(2, col), Cells(Rows.Count, col).End(xlUp)).Formula = _
Cells(2, col).Formula


--------------------
 
Hi Colo,

I placed my cursor at Cell M2 that has a formula and run the code belo
but it doesn't copy down the formula !

Is there something missing from your code ?
Hope you can help. Thanks.


Sub Cell2CopyToEnd()

Dim col As Long
col = ActiveCell.column
Range(Cells(2, col), Cells(Rows.Count, col).End(xlUp)).Formula = _
Cells(2, col).Formula

End Su
 
Hi

Let me know one thing pls. how can i know the last row.? I mean which
column should be based for getting the last row?
 
Back
Top