Hari

The Macro recorder is oft overlooked as a great way of learning code. I
still use it regularly for the less often used objects.

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England

"Hari Prasadh" wrote in message
...
Hi Nick,

Thnx a ton for your help. Just what I needed. It pastes the data from one
row then to second and so on.

Im learning the * P's and Q's* of VBA through macro recording, hence the
clutter. Will try to get comfortable with using object variables by using
F8 in your code. (that way I can make better sense)

Thanks a lot,
Hari
India

"Nick Hodge" wrote in message
...
Hari

There is little need to activate or select. Each time you do Excel
has to re-draw the screen which takes considerable time.

If you are likely to switch back and forth between workbooks you can
assign them to object variables and use these to refer to them quickly
and accurately without selection or activation.

I suspect the code below, which while definately not the best will run
about 100 times quicker than loads of activating and selecting

Sub CopyNonBlankData()
Dim NewWb As Workbook, NewWks As Worksheet
Dim CurrWks As Worksheet, rng As Range
Dim lLastFixedRow As Long, iLastCol As Integer
Dim lLastVariableRow As Long

Set CurrWks = ThisWorkbook.Worksheets("basesheet")
Set NewWb = Workbooks.Add
Set NewWks = NewWb.Worksheets(1)
lLastFixedRow = CurrWks.Range("O65536").End(xlUp).Row

For Each rng In CurrWks.Range("O2:O" & lLastFixedRow)
iLastCol = rng.Offset(0, 6).End(xlToLeft).Column
lLastVariableRow = NewWks.Range("A65536").End(xlUp).Row + 1
rng.Resize(, iLastCol - 14).Copy
NewWks.Range("A" & lLastVariableRow).PasteSpecial _
Paste:=xlPasteValues, Transpose:=True
Next rng
End Sub


Nick Hodge
Microsoft MVP - Excel
Southampton, England

On Sun, 30 Jan 2005 19:57:19 +0530, "Hari Prasadh"
wrote: