I a macro which will copy the formula in the first row of a column down the
rest of the rows in that column as far as the last non-empty row in the
adjacent column, which is a variable number of rows.
Any ideas?

Sub CopyFormula()

With Range(Range("C2"), Range("C2").End(xlDown))
.FormulaR1C1 = Range("C1").FormulaR1C1

End With

End Sub




"SoozeeC" wrote:

I a macro which will copy the formula in the first row of a column down the
rest of the rows in that column as far as the last non-empty row in the
adjacent column, which is a variable number of rows.
Any ideas?

A better way is to put the formula in the macro instead

Sub copyformula()
lr = Cells(Rows.Count, "k").End(xlUp).Row
Cells(1, "L").Resize(lr).Formula = "=k1*2"
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"SoozeeC" wrote in message
...
I a macro which will copy the formula in the first row of a column down the
rest of the rows in that column as far as the last non-empty row in the
adjacent column, which is a variable number of rows.
Any ideas?

You can try a few different things:
Fill Down Values:

Sub FillBlanks()
Dim rr, r As Range
Set r = Range("A2:A500")
For Each rr In r
If rr.Value = "" Then
rr.value = rr.offset(-1,0).value
End If
Next rr
End Sub


Find Last Used Cell:
Sub FindLastCell1()
Cells(Rows.Count, "A").End(xlUp).Select
End Sub

Sub FindLastCell2()
Range("A:A").Find("*", Cells(1), _
xlValues, xlWhole, xlByRows, xlPrevious).Select
End Sub


Range is A:N
FirstRow = 3
LastRow = Range("B" & Rows.Count).End(xlUp).Row
Set SortRange = Range("A" & FirstRow & ":N" & LastRow)


Hope these samples shed some light on the subject!
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Patrick Molloy" wrote:

Sub CopyFormula()

With Range(Range("C2"), Range("C2").End(xlDown))
.FormulaR1C1 = Range("C1").FormulaR1C1

End With

End Sub




"SoozeeC" wrote:

I a macro which will copy the formula in the first row of a column down the
rest of the rows in that column as far as the last non-empty row in the
adjacent column, which is a variable number of rows.
Any ideas?

Hi, fairly new to VBA,

I have 5 worksheets where I have to filter and copy data from column B, C
and D to another worksheet (named: Customers) in same workbook.

Have got the code to move what data I want but need the data in each
worksheet to go into the Customers worksheet by filling in from the next
blank cell. If I copy data from worksheet A it holds but then when I copy
data from worksheet B it copies over the data...I need code please to take
the data from the each worksheet and just copy it down: My code is set as:
Sub CopySignificant()
'Copy cells of cols A,F,E,D from rows containing "Significant" in
'col D of the active worksheet (source sheet) to cols
'A,B,C,D of Sheet2 (destination sheet)
Dim DestSheet As Worksheet
Set DestSheet = Worksheets("Customers")

Dim sRow As Long 'row index on source worksheet
Dim dRow As Long 'row index on destination worksheet
Dim sCount As Long
sCount = 0
dRow = 1

For sRow = 1 To Range("G57").End(xlUp).Row
'use pattern matching to find "Significant" anywhere in cell
If Cells(sRow, "G") Like "*GIRO*" Then
sCount = sCount + 1
dRow = dRow + 1
'copy cols A,F,E & D
DestSheet.Cells(dRow, "A") = Cells(sRow, "B")
DestSheet.Cells(dRow, "B") = Cells(sRow, "C")
DestSheet.Cells(dRow, "C") = Cells(sRow, "D")
End If
Next sRow

MsgBox sCount & " Giro Customers copied", vbInformation, "Transfer Done"

End Sub

This appears in each worksheet, can anyone help?

Thanks

Craigos

"Don Guillett" wrote:

A better way is to put the formula in the macro instead

Sub copyformula()
lr = Cells(Rows.Count, "k").End(xlUp).Row
Cells(1, "L").Resize(lr).Formula = "=k1*2"
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"SoozeeC" wrote in message
...
I a macro which will copy the formula in the first row of a column down the
rest of the rows in that column as far as the last non-empty row in the
adjacent column, which is a variable number of rows.
Any ideas?

If desired, send your file to my address below along with this msg and
a clear explanation of what you want and before/after examples.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Craigos" wrote in message
...
Hi, fairly new to VBA,

I have 5 worksheets where I have to filter and copy data from column B, C
and D to another worksheet (named: Customers) in same workbook.

Have got the code to move what data I want but need the data in each
worksheet to go into the Customers worksheet by filling in from the next
blank cell. If I copy data from worksheet A it holds but then when I copy
data from worksheet B it copies over the data...I need code please to take
the data from the each worksheet and just copy it down: My code is set as:
Sub CopySignificant()
'Copy cells of cols A,F,E,D from rows containing "Significant" in
'col D of the active worksheet (source sheet) to cols
'A,B,C,D of Sheet2 (destination sheet)
Dim DestSheet As Worksheet
Set DestSheet = Worksheets("Customers")

Dim sRow As Long 'row index on source worksheet
Dim dRow As Long 'row index on destination worksheet
Dim sCount As Long
sCount = 0
dRow = 1

For sRow = 1 To Range("G57").End(xlUp).Row
'use pattern matching to find "Significant" anywhere in cell
If Cells(sRow, "G") Like "*GIRO*" Then
sCount = sCount + 1
dRow = dRow + 1
'copy cols A,F,E & D
DestSheet.Cells(dRow, "A") = Cells(sRow, "B")
DestSheet.Cells(dRow, "B") = Cells(sRow, "C")
DestSheet.Cells(dRow, "C") = Cells(sRow, "D")
End If
Next sRow

MsgBox sCount & " Giro Customers copied", vbInformation, "Transfer Done"

End Sub

This appears in each worksheet, can anyone help?

Thanks

Craigos

"Don Guillett" wrote:

A better way is to put the formula in the macro instead

Sub copyformula()
lr = Cells(Rows.Count, "k").End(xlUp).Row
Cells(1, "L").Resize(lr).Formula = "=k1*2"
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"SoozeeC" wrote in message
...
I a macro which will copy the formula in the first row of a column down
the
rest of the rows in that column as far as the last non-empty row in the
adjacent column, which is a variable number of rows.
Any ideas?