Copy and Paste Row and Columns(with formulas) and Paste down to the last row in VBA

Question

Firstly, I have multiple sheets with values and formula's. I have used VBA to pull down clean data, which is working fine. For Example "Sheet1", From Row 3 to 100 and Column H to K is where I have the values. Now, I have a formula on row 3 and Column B to F that is linked to the values on Columns H to K. I am having problem copying row 3 and pasting the formulas down to the last row on the sheet.

Secondly, I have 12 worksheets. They have different values and formula's from "sheet 1". But I want the same action to apply to all 12 sheets like "sheet 1".

Here is the code I using to copy and paste my values ( but it copies only one row. I want it to go to bottom of sheet) :

Sub formula_format (data_sheet As String, row_num_start As Integer, column_num_start As Integer, row_num_end As Integer, column_num_end As Integer)

Sheets(data_sheet).Select

 For Row = row_num_start To row_num_end

    If Cells(Row, column_num_start).Value2 = "" Then

       Range(Cells(Row - 1, column_num_start), Cells(Row - 1, column_num_end)).Copy
       ActiveSheet.Cells(Row, column_num_start).Select
       ActiveCell.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
       ActiveCell.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

       Exit For

    End If

Next Row

End Sub

This code copy the first line and pastes it to the next line. I need to continue to the last line of sheet.

Thanks in advance. Sorry for the long post. Its my first post!

with the Exit For inside your IF block the code will exit the For loop the first time the row and column = "" thus stopping the code. SO, my first suggestion is to remove the Exit For — Scott Holtzman
Thanks Scott! I have done what you have suggest. It works (which makes me pleased as I have been looking at this problem for 2 days)!! — James Jordan

Scott Holtzman Scott Holtzman · Accepted Answer · 2016-03-15T15:22:48

In addition to my comment above, I would like to offer an alternative method, which is much simpler, faster and easier to maintain, if I understand correctly what you are trying to do (copy formulas across columns down a given length of rows).

Sub formula_format(data_sheet As String, row_num_start As Integer, column_num_start As Integer, row_num_end As Integer, column_num_end As Integer)

With Sheets(data_sheet)

    .Range(.Cells(row_num_start, column_num_start), .Cells(row_num_start, column_num_end)).AutoFill _
        Destination:=.Range(.Cells(row_num_start, column_num_start), .Cells(row_num_end, column_num_end))

End With


End Sub

Copy and Paste Row and Columns(with formulas) and Paste down to the last row in VBA

1 Answers