Copy a row down a column for the same number of rows of data in another workshee
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 |
Copy a row down a column for the same number of rows of data in another workshee
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 |
Copy a row down a column for the same number of rows of data in another workshee
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?? |
Copy a row down a column for the same number of rows of data in another workshee
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 |
Copy a row down a column for the same number of rows of data in another workshee
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. |
Copy a row down a column for the same number of rows of data in another workshee
' 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. |
Copy a row down a column for the same number of rows of data in another workshee
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 |
All times are GMT +1. The time now is 02:48 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com