pasting a forumla down for a known dataset

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

dkintheuk - 20 Mar 2006 14:58 GMT

Hi all,

First post - so please be gentle!

I've a spreadsheet with a macro that works as follows:

1. the user pastes some data into B4 to Bn (variable range) and for a
fixed set of columns
2. the formula that is in cell A4 needs to be copied down to all the
rows of data.

Below is the code i use...

Sheets("TB").Select
LastRowTB = Cells.Find(What:="*", After:=[A1],
SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Range("A4").Select
Selection.Copy
For rowcounter = 1 To LastRowTB - 3
Range("A4").Offset(rowcounter, 0).Select
ActiveCell.PasteSpecial Paste:=xlAll
Next

the problem is that when it runs, it takes forever to move row to row -
up to 2 or 3 seconds per row. I've used debug lines to show that the
line ActiveCell.PasteSpecial Paste:=xlAll is at fault but this has not
helped as i cannot see why this is failing.

I have been thinking about defining the whole range from A4 to An and
then pasting but i can't work out how to define the range and then use
to as a paste location.

Any help gratefully received.

Rob.

Reply to this Message

Carim - 20 Mar 2006 15:48 GMT

Hi Rob,

My proposal would be the following :

Sub Macro1()
Dim i As Integer 'for the LastrowTB
Sheets("TB").Select
i = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows,
SearchDirection:=xlPrevious).Row
Range("A4").Select
Selection.Copy
ActiveCell.Offset(1, 0).Range("A1:A" & i - 3).Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.Offset(-1, 0).Range("A1").Select
End Sub

HTH
Cheers
Carim

Reply to this Message

dkintheuk - 20 Mar 2006 15:58 GMT

In fact i think i've shrunk it even more...

Sheets("TB").Select
Range("A4").Select
Selection.Copy
Range("A4:A" & (LastRowTB - 3)).Select
Selection.PasteSpecial Paste:=xlAll

seems to work as expected.

Cheers for the advice... once you showed that i could just put the
vairable into the range string then it all becomes much easier.

Thanks,

Rob.

Reply to this Message

funkymonkUK - 20 Mar 2006 15:55 GMT

I think this is what you are looking for. If this is list of data whic
is updated regular then this will find the last row. It will then ente
in a forumla and fill that formula down to the last cell. it will the
select that row and paste special the value of cell.

Hope this helps

Function lastRowpub(colnum As Long, Optional sh As Worksheet) As Long
' Count Rows in table
If sh Is Nothing Then Set sh = ActiveSheet
lastRowpub = sh.Cells(sh.Rows.Count, colnum).End(xlUp).Row
End Function

Sub Pasting
' Find the last row of data
ilastrow = lastRowpub(1, Worksheets("Sheet1"))
' Enter in formula copy formula down to the last row and then copy an
paste value.
Range("A1").Formula = "Sum of Column b & C"
Range("A2").formula="=B2+c2"
Range("A2").AutoFill Destination:=Range("A2:A" & ilastrow)
Range("A2:A" & ilastrow).Copy
Range("A2:A" & ilastrow).PasteSpecial Paste:=xlValues
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Exit su

Reply to this Message