首页 文章

将活动工作表中的值复制到新的Excel文件中

提问于
浏览 次
0

我有一个名为Final_Sheet的工作表，我想在该工作表上创建一个按钮并执行以下操作

选择单元格区域A1：D30并从单元格中拾取值并创建一个新的Excel文件，并将复制的单元格值粘贴到已创建的Excel文件的Sheet1中 . 我能够做到这一点，进一步我无法理解做什么可以请任何人帮助我吗？

Private Sub Button1_Click()
Dim rownum As Integer
Dim selection As Range

On Error Resume Next
Application.ScreenUpdating = False
Application.DisplayAlerts = False

rownum = InputBox("Row No to Copy :", "OK")
selection = ActiveSheet.Range(Cells(1, 1), Cells(rownum, 10)).Select
selection.Copy


Flname = InputBox("Enter File Name :", "Creating New File...")

MsgBox ("Output File Created Successfully")
If Flname <> "" Then
    Set NewWkbk = Workbooks.Add
    ThisWorkbook.Sheets(1).Range("D1:D30").Copy Before:=NewWkbk.Sheets(1)
    NewWkbk.Sheet(1).Select
    Cells(1, 1).Activate
    selection.PasteSpecial xlPasteValues

    NewWkbk.SaveAs ThisWorkbook.Path & "\" & Flname

    ActiveWorkbook.Close
End If

End Sub
excel vba excel-vba

1 回答

  • 0

    下面的代码应该按照你的要求进行 .

    EDIT: Pastes values only

    Private Sub Button1_Click()
    'Dim Variables
    Dim ws As Worksheet
    Dim rng As Range
    Dim wbNew As Workbook, wbCurrent As Workbook
    Dim strFileName As String

    'Assign object variables
    Set wbCurrent = ActiveWorkbook
    Set ws = wbCurrent.ActiveSheet
    Set rng = ws.Range("A1:D30") 'Amend range if needed

    'Get desired file path from user
    strFileName = InputBox("Enter File Name: ", "Creating New File...")
    If strFileName <> "" Then
        'Create new Workbook and paste rng into it
        Set wbNew = Workbooks.Add
        rng.Copy
        wbNew.Sheets(1).Range("A1:D30").PasteSpecial xlValues

        'Save new workbook using desired filepath
        wbNew.SaveAs wbCurrent.Path & "\" & strFileName
    End If
End Sub

    我不清楚你希望用 ActiveWorkbook.Close 关闭哪个工作簿的问题，但你可以根据需要在 wbNew.SaveAs ... 下轻松添加 wbNew.Close 或 wbCurrent.Close .

    回复于

相关问题