Click here to Skip to main content
15,391,989 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I need to copy a row of formulas in 13 cells to a second spreadsheet in he same workbook. I know loops are inefficient and this is clearly an example!

Is there a different way to go here? I inactivate screen updating and calculations, but that doesn't help much. I do limit the number of rows needed by counting them in the second sheet and limiting the copying to that amount of rows.

I expect 1000-2000 rows, depending on data. 500 rows is greater than 4 minutes.

Any ideas?

Thanks,
Sleeper

What I have tried:

VBScript
<pre>Sub Find_Last_Row()

Dim repeatCopy As Integer

Dim Last_Row As Long
    On Error Resume Next
    Last_Row = Cells.Find(What:="*", After:=Range("A1"), _
                    LookAt:=xlPart, LookIn:=xlFormulas, _
                    SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _
                    MatchCase:=False).Row
    On Error GoTo 0
    MsgBox "Last Row: " & Last_Row
    
'Copy Header to Row 1
Sheets("fmulas").Range("Y1:AI1").Copy Destination:=Sheets("data").Range("Y1:AI1")

Application.ScreenUpdating = False
Application.Calculation = xlManual

'temp********
Last_Row = 500

For repeatCopy = 2 To Last_Row

'set Range to formula's corresponding Column
'Start in Row 2

Sheets("fmulas").Range("Y2:AI2").Copy Destination:=Sheets("data").Range("Y2:AI" & repeatCopy)

Next repeatCopy

Application.ScreenUpdating = True
Calculate
Application.Calculation = xlAutomatic

    
End Sub
Posted
Updated 28-Jun-22 1:15am
v2
Comments
0x01AA 27-Jun-22 16:06pm
   
Something like that? Worksheet.UsedRange property (Excel) | Microsoft Docs[^]
Btw. UsedRange returns the number of columns and rows

Quote:
Vba loop optimization

Best loop optimization is no loop, Excel knows number of used rows of sheet.
VB
Last_Row = ActiveSheet.UsedRange.Rows.Count
   
Be aware that sometimes UsedRange can be overstated. To demonstrate this use Ctrl-End in your worksheet to "find" the last cell used. Now type something in to a cell lower and to the right. Delete what you have just typed in, then select cell A1. Now use Ctrl-End again - Excel will (usually) take you the now empty cell that you just typed into and cleared out.

There are ways to reset that, that usually involve having to save the file. But in VBA you can ensure that it is always reset by referencing the UseRange at the start of your sub
VB
ActiveSheet.UsedRange


After that, instead of using loops you should treat the Range as a set, and there is no need to use .Copy, you can just assign the formulae - text (i.e. anything that is not really a formula) will still be "copied" over. E.g. here is a sample module
VB
Option Explicit

Sub Demo()

    With ThisWorkbook.Sheets(1)

        'This will ensure the UsedRange is correctly reset
        .UsedRange
    
        'The number of rows and columns are properties of UsedRange
        Dim r As Long, c As Long
        r = .UsedRange.Rows.Count
        c = .UsedRange.Columns.Count
        
        '"Find" the address of the last cell used
        Dim lastcell As Range
        Set lastcell = .Range("A1").Offset(r, c - 1)
        
        'Define the source and the target and copy over the formulae
        Dim source As Range, target As Range
        Set source = .Range("A1:" & lastcell.Address)
        Set target = ThisWorkbook.Sheets(2).Range(source.Address)
    
        target.Formula = source.Formula
    
    End With

    'Alternatively - but only if you want to copy EVERYTHING
    Set source = ThisWorkbook.Sheets(1).UsedRange
    Set target = ThisWorkbook.Sheets(3).Range(source.Address)
    target.Formula = source.Formula

End Sub
   
Comments
Sleeper 11888211 1-Jul-22 18:57pm
   
@Chill60-
I looked at your code and realized the code transfers all the data in the source to target. What I have is a single row of target formulas Y2 through AI2. A second worksheet has data in a variable range, say A1:X1000. Each target row needs the formula row transferred from the source Y2:AI2, down the rows to the Last_Row, meaning one row of formulas for each row of data. I do this by looping through a copy/paste of source's single row, copied into each row of target. I test for the end row, so it is exactly the number of rows (copies) I need. I disable calculation and screen updates before the loops, but it is still slow. (sorry if duplicate. It seemed to hav frozen) S.
CHill60 2-Jul-22 3:49am
   
Nope - it copies the formulas. Just adjust the ranges to reflect the columns you want and use the technique inside the "with" not the "alternatively..." bit
Sleeper 11888211 5-Jul-22 10:23am
   
I have this working, though I am partially hard coding the range. I do find the last row needed by code, so that's the hard part. I am trying to see what I have wrong from your code.

Aside from that, how do I maintain the source's formatting?
Thanks,
Paul
CHill60 5-Jul-22 12:46pm
   
Keep formatting in questions here? When you paste stuff in you should get a pop-up menu. Iusually use "Paste as is" then select all the code and use the formatting tool "Code" at the top of the input box. You will see a list of languages appear

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900