VBA to skip lines

[chrysti]

I need to write a macro that will reference a cell that I have counted how many rows my data needs to copied down to and stop when it hits that cell number. Example my reference cell ia A1 and it calculates there are 657 cells of data. I want to have my formulas on another tab paste down 657 cells...

Please help.

Thanks in advance
C

 

[Ralajer]

C,

The below code could be improved and generalized a little more but it does work in my tests. It assumes continuous data on each sheet from A1 as I am using currentRegion to bound the data. Change the base from A1 if needed.

Sub fillToOtherSheetRow()

    Dim rng1 As Range
    Dim rng2 As Range
    Dim rng2Paste As Range
    
    Dim LR1 As Long
    Dim LR2 As Long
    Dim LC2 As Integer
    
    Set rng1 = Sheets("Sheet1").Range("A1").CurrentRegion
    Set rng2 = Sheets("Sheet2").Range("A1").CurrentRegion

    LR1 = rng1.Row + (rng1.Rows.Count - 1)
    LR2 = rng2.Row + (rng2.Rows.Count - 1)
    LC2 = rng2.Column + (rng2.Columns.Count - 1)
      
    With Sheets("Sheet2")
        Set rng2Paste = .Range(.Cells(LR2 + 1, rng2.Column), .Cells(LR1, LC2))
    End With

    rng2.Rows(rng2.Rows.Count).Copy rng2Paste

End Sub

Too Tired Edit
I am probably to tired to post as I am not sure the above is a solution to the specifics of your problem or not. On first reading I thought it was what you wanted but after posting and rereading your question I am not sure. Either way let me know