excel - 将特定行从工作簿复制到 x 个新工作簿（每行一个），仅粘贴为格式/值

Question

VBA初学者。我正在尝试做的事情：

• 复制当前工作表中的前 5 行 _ 行“x”，然后粘贴到新工作簿中
• 新工作簿应保存在同一目录中
• 这应该对前 5 行以下的每一行重复，即第 1-5 + 6 行、第 1-5 + 7 行、第 1-5 + 8 行等。
• 将行粘贴到新工作簿时，我不想复制公式，只复制格式和值

这是我到目前为止所拥有的：

Sub CommandButton1_Click()

    Dim MyBook As Workbook, newBook As Workbook
    Dim FileNm As String

    Set MyBook = ThisWorkbook

    FileNm = ThisWorkbook.Path & "\" & "TEST-BOOK.xlsx"
    Set newBook = Workbooks.Add

    With newBook
        MyBook.Sheets("Sheet1").Rows("1:5").Copy .Sheets("Sheet1").Rows("1")

        'Save new wb
        .SaveAs Filename:=FileNm, CreateBackup:=False

        .Close Savechanges:=False
    End With

End Sub

它复制第 1-5 行，但我不知道如何添加动态额外行——它还复制所有公式并嵌入它们。假设文件名也必须处于某种循环中？谢谢你。

标签： excelvba

Answer 1

希望这可以帮助，

Sub CommandButton1_Click()

    Dim wb As Workbook, FileNm As String, LastRow As Long, Headers As Range, wbTemp As Workbook, i As Long

    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False

    Set wb = ThisWorkbook

    'lets suppose your data is in the first worksheet of your book
    With wb
        LastRow = .Sheets(1).Cells(.Sheets(1).Rows.Count, 1).End(xlUp).Row - 5 'this is to count how many rows you've got
        Set Headers = .Sheets(1).Rows("1:5") 'set the headers to copy them every iteration
    End With

    'copy each row + headers in a new workbook
    For i = 1 To LastRow
        FileNm = wb.Path & "\" & "TEST-BOOK" & i & ".xlsx" 'add the i to number every workbook from 1 to extra rows you have
        Set wbTemp = Workbooks.Add 'add a new workbook
        Headers.Copy 
        wbTemp.Sheets(1).Rows(1).PasteSpecial xlPasteValues 'paste the headers
        wb.Sheets(1).Rows(5 + i).Copy 
        wbTemp.Sheets(1).Rows(6).PasteSpecial xlPasteValues 'copy the next row in the iteration
        wbTemp.SaveAs FileNm
        wbTemp.Close
        Set wbTemp = Nothing
    Next i

    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

End Sub