pasting a forumla down for a known dataset

[dkintheuk]

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.

[Carim]

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

[funkymonkUK]

I think this is what you are looking for. If this is list of data which is updated regular then this will find the last row. It will then enter in a forumla and fill that formula down to the last cell. it will then 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 and 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 sub

[dkintheuk]

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.