复制并粘贴行和列(带有公式)，然后粘贴到VBA中的最后一行 [英] Copy and Paste Row and Columns(with formulas) and Paste down to the last row in VBA

问题描述

首先，我有多个包含值和公式的工作表.我已经使用VBA拉下干净的数据，这工作正常.例如"Sheet1"，从第3行到100和从H列到K列是我的值.现在，我在第3行和B到F列有一个公式，该公式链接到H到K列的值.我在复制第3行并将公式粘贴到工作表的最后一行时遇到问题.

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.

第二，我有12个工作表.它们具有与工作表1"不同的值和公式.但是我希望将相同的操作应用于所有12个工作表，例如工作表1".

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!

推荐答案

除了我上面的评论，我想提供一种替代方法，如果我正确理解了您的意思，那么它将更简单，更快速，更易于维护.正在尝试这样做(在给定的行长度下跨列复制公式).

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

这篇关于复制并粘贴行和列(带有公式)，然后粘贴到VBA中的最后一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！