0

I have a workbook with around 75 worksheets, that all contain different sorts of data that get updated every month. Some of the data gets updated automatically, but in nearly every worksheet, there are formulas that need to be dragged. I need to drag the formulas down 30 rows in every worksheet.

So I want to loop through each worksheet and then through each column that contains formulas to drag. I have already marked each column to drag with the letter "F" in row 1 of the column so that I can put an IF statement to only drag those columns.

My problem now is that I do not know how to select the last cell of column with a formula in it and then drag it down 30 rows.

Sub Drag_Formulas()

    'Number of Worksheets

        Dim i As Integer
        Dim ws_num As Integer
        ws_num = ThisWorkbook.Worksheets.Count

    'Number of columns

        Dim c As Integer

    'Loop 1

        For i = 1 To ws_num
            ThisWorkbook.Worksheets(i).Activate
                   For c = 1 To 105

                        If Cells(1, c).Value = "F" Then
                            Cells(20000, c).Select        'I used 20000 since no worksheet has data going as far as 20000, so that way I am sure to get the last cell with data
                            Selection.End(xlUp).Select
                            Selection.Copy

                        Else
                            Next c
                        End If

                    Next c

End Sub

So I got as far as copying the last cell with a formula of a column, but I do not know how to drag it down 30 rows, since with this code I do not know what row the last cell is on.

Thanks for the help!

Mikku
  • 6,538
  • 3
  • 15
  • 38
TristB
  • 17
  • 6

1 Answers1

1

As mentioned, currently the macro doesn't actually do anything. However, assuming your formula for each column is in row 2, and you want to drag that down to the last row in that column, you could use the following.

Sub drag()
Dim i As Long, col As Long, lastCol As Long, lastRow As Long
Dim copyRowAmt
Dim ws As Worksheet
Dim fmlaCell As Range

copyRowAmt = 30

For Each ws In ThisWorkbook.Worksheets
    With ws
        ' This assumes your column headers are in row 1,
        ' to get the total number of columns dynamically
        lastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
        For col = 1 To lastCol
            If .Cells(1, col).Value = "F" Then
                lastRow = .Cells(.Rows.Count, col).End(xlUp).Row
                .Range(.Cells(lastRow, col), .Cells(lastRow + copyRowAmt, col)).Formula = _
                    .Cells(lastRow, col).Formula
            End If
        Next col
    End With
Next ws
End Sub

Note this also avoids using .Select`.Activate`

BruceWayne
  • 22,923
  • 15
  • 65
  • 110
  • The problem is that the formula that I want to drag is in a different row in each worksheet and I only want to drag it 30 rows down. – TristB Aug 02 '19 at 12:08
  • @TristB - Is it the *only* formula in that column? Or are there many formulas, but you somehow know which formula you want to drag down? – BruceWayne Aug 02 '19 at 13:56
  • Every column has a different formula, but in a specific column, it is the same formula that I want to drag. – TristB Aug 02 '19 at 14:04
  • @TristB - Can you describe how you know which row, per column, has the formula you want to drag down? It shouldn't be hard to tweak the macro, but there needs to be some way to know what formula you're looking to copy down 30 rows. – BruceWayne Aug 02 '19 at 14:05
  • I marked every column that has a formula to drag down with the letter "F" in row1 and in the macro I have an If statement to only loop through the columns that have the letter "F" in row 1. Then I use the code : LastRow = Cells(Rows.Count, c).End(xlUp).Row Cells(LastRow, c).Copy – TristB Aug 02 '19 at 14:09
  • The last cell of each column I am looking to drag contains the formula to drag. – TristB Aug 02 '19 at 14:14
  • @TristB - Ah! So if the last cell is, row 100, you want to copy row 100 down 30 rows, to row 130? If the last cell is row 123, you want to drag down until 153? – BruceWayne Aug 02 '19 at 14:28
  • Yes exactly! But I have over 750 columns with formulas to drag down and they all start on different rows. – TristB Aug 02 '19 at 14:30
  • @TristB - Yeah, that's no worries. I've updated the code in my answer. That should do it for you. – BruceWayne Aug 02 '19 at 14:36
  • Thank you! It works now, but I now have a new problem.. if you want to go see my new post, maybe you could help. https://stackoverflow.com/questions/57328463/how-to-copy-the-last-non-blank-cell-in-a-column-that-contains-blank-cells-with-f/57328782#57328782 – TristB Aug 02 '19 at 14:57
  • @TristB - I'll take a look. If this works as the Answer for this question, then you can mark as The Answer by clicking the check mark left of the post. – BruceWayne Aug 02 '19 at 15:06
  • Just did it! I am new to StackOverflow, so I didn't know about that functionality! Thank you very much for the help! – TristB Aug 02 '19 at 15:09