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

Fredriksson via OfficeKB.com wrote:
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

I'm assuming the formulas/values you are trying to copy from worksheet2
start at A10 and go down the page. You then want to copy these
formulas/values and paste them into Worksheet1.

If the data in worksheet2 is continous why not just do:

Range("A10").Select
Range(Selection, Selection.End(xlDown)).Select

This doesn't matter how long your data column is, as long as it is
continuos, ie no gaps. And then simply copy and paste into your
Worksheet2 as values or with formulas, how ever you want.

Is this of any help??

Worksheet 2 is blank accept for the formulas is Row A10 that refer to the
Data in row A2 in Worksheet 1.
I need to copy the formulas in Worksheet2 RowA10 to RowA11... Row??? so that
each row will reference a different row in Worksheet 2. In worksheet2, I
need to figure out a way to count the number of rows of data so I know how
many rows in Worksheet1 I need to copy RowA10 DOWN. Since the number of rows
in worksheet2 varies from month to month, I need a way of doing this without
having fixed ranges.

I hope this makes more sense.

Stopher wrote:
I'm assuming the formulas/values you are trying to copy from worksheet2
start at A10 and go down the page. You then want to copy these
formulas/values and paste them into Worksheet1.

If the data in worksheet2 is continous why not just do:

Range("A10").Select
Range(Selection, Selection.End(xlDown)).Select

This doesn't matter how long your data column is, as long as it is
continuos, ie no gaps. And then simply copy and paste into your
Worksheet2 as values or with formulas, how ever you want.

Is this of any help??

--
Message posted via http://www.officekb.com

Fredriksson via OfficeKB.com wrote:
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

Sorry I reread your post and misinterpreted what you wrote.

Try this:

Sub CopyFormulas()

' This will give you the number of rows in GLFBCALCO sheet
Sheets("GLFBCLACO").Range("a10").select
i = Range(Selection, Selection.End(xlDown)).Count

' Then use this to loop through the sheets and copy from one to the
other
j = i+10
Endrange = "A" & j
Sheets("Template").Range("A10:Endrange").copy

Now I don't know where your pasting this so you need to select the
sheet and paste special formulas to the cells.

This is more of what you are trying to achieve I think.

' Then use this to loop through the sheets and copy from one to the
other
Don't worry about this statement I took the loop out after i worked
out what you wanted.

Thanks a lot. I appreciate all of your suggestions.

Stopher wrote:
' Then use this to loop through the sheets and copy from one to the
other
Don't worry about this statement I took the loop out after i worked
out what you wanted.

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