I am importing a file that varies in length from month to month into
Worksheet1. In worksheet2, I need to copy the formulas in Row A10 down the
same number of rows in Worksheet1. I do not want a formula in Worksheet 2
referencing a empty row in Worksheet1.

If there are 100 rows of data in Worksheet1 then I want copy the formula in
100 rows in Worksheet2

This is the code I have been playing with

Sub CopyRow()
'
' CopyRow Macro
' Macro recorded 9/29/2006 by dfredrik
'

'
Dim rng As Range
Dim CurCell As Object
Dim RowNum As Integer
Dim CellsToLoop
Dim Counter As Integer
With Worksheets("GLFBCALO")
Set rng = .Range(.Range("A2"), .Cells(Rows.Count, 1).End(xlUp))
End With
Counter = 10
For Each CellsToLoop In rng.Cells
Range("FormulaRow").Copy
'Selection.Copy
Range("FormulaRow").Copy
Set CurCell = Worksheets("Template").Cells(Counter, 1)
CurCell.Paste
ActiveSheet.Paste
Counter = Counter + 1
Next

End Sub

In the above code the Worksheet1 is GLFBCALO and Worksheet2 is Template.

Any help would be appreciated

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200609/1

Hi Fredriksson. i would do it this way:

Sub CopyRow()
Dim t
Sheets("GLFBCALO").Select
For t = 10 To Cells(65536, 1).End(xlUp).Row
Cells(t, 1).Copy Destination:=Sheets("Template").Cells(t, 1)
Next
End Sub




"Fredriksson via OfficeKB.com" skrev:

I am importing a file that varies in length from month to month into
Worksheet1. In worksheet2, I need to copy the formulas in Row A10 down the
same number of rows in Worksheet1. I do not want a formula in Worksheet 2
referencing a empty row in Worksheet1.

If there are 100 rows of data in Worksheet1 then I want copy the formula in
100 rows in Worksheet2

This is the code I have been playing with

Sub CopyRow()
'
' CopyRow Macro
' Macro recorded 9/29/2006 by dfredrik
'

'
Dim rng As Range
Dim CurCell As Object
Dim RowNum As Integer
Dim CellsToLoop
Dim Counter As Integer
With Worksheets("GLFBCALO")
Set rng = .Range(.Range("A2"), .Cells(Rows.Count, 1).End(xlUp))
End With
Counter = 10
For Each CellsToLoop In rng.Cells
Range("FormulaRow").Copy
'Selection.Copy
Range("FormulaRow").Copy
Set CurCell = Worksheets("Template").Cells(Counter, 1)
CurCell.Paste
ActiveSheet.Paste
Counter = Counter + 1
Next

End Sub

In the above code the Worksheet1 is GLFBCALO and Worksheet2 is Template.

Any help would be appreciated

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200609/1