时间戳

首页 > 解决方案 > VBA - 代码将粘贴复制到代码中未指定的列中

vba - VBA - 代码将粘贴复制到代码中未指定的列中

问题描述

我正在使用下面的代码将combinedWorkbook中的B列复制到ThisWorkbook中的B列，但是在运行宏时，它似乎将B列粘贴到ThisWorkbook的C列中以及粘贴到B列中。我已经逐步完成了代码和它工作正常。这似乎很奇怪，如果它也粘贴到 ThisWorkbook 的 C 列中，将不胜感激。

Sub ImportWriteOffs()
    Dim filter As String
    Dim caption As String
    Dim combinedFilename As String
    Dim combinedWorkbook As Workbook
    ' Open BRAM Report Source Data
    MsgBox ("Select 'SRMF0035 BRAM Pre Repurchase'")
    filter = "Text files (*.*),*.*"
    caption = "Select 'SRMF0035 BRAM Pre Repurchase'"
    combinedFilename = Application.GetOpenFilename(filter, , caption)
    If combinedFilename <> "False" Then
        Set combinedWorkbook = Application.Workbooks.Open(combinedFilename)
    Else
        MsgBox "No file was uploaded", vbExclamation
        GoTo LastLine
    End If
    If combinedWorkbook.Worksheets(1).Range("D7").Value = "Periodic Insurance" Then
        ' Copy and Paste into working file
        Sheets("Tabular Version").Select
        Range("B10:B100000").Select
        Selection.Copy
        ThisWorkbook.Activate
        Sheets("Input - Write offs").Select
        Range("B10:B100000").Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
        combinedWorkbook.Close False
        ' Delete last row
        ThisWorkbook.Activate
        Sheets("Input - Write offs").Select
        Range("B10").Select
        Selection.End(xlDown).Select
        Selection.EntireRow.Delete
    Else
        MsgBox "Incorrect File Selected"
        combinedWorkbook.Close False
        Exit Sub
    End If
LastLine:
End Sub

标签： vbaexcelcopycopy-pastepaste

解决方案

你可以试试这个。请注意，您不需要.Select一个单元格来复制它。它违背了VBA! 切入正题：说明范围并复制它。无需选择。

此外，不需要GoTo像臭名昭著的@ashleedawg 所说的那样，只是Exit Sub在需要的时候。

Sub ImportWriteOffs()

Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Input - Write offs")

Dim filter As String, caption As String, combinedFilename As String
Dim combinedWorkbook As Workbook, ws2 as Worksheet

MsgBox ("Select 'SRMF0035 BRAM Pre Repurchase'")
    filter = "Text files (*.*),*.*"
    caption = "Select 'SRMF0035 BRAM Pre Repurchase'"
    combinedFilename = Application.GetOpenFilename(filter, , caption)

If combinedFilename <> "False" Then
    Set combinedWorkbook = Application.Workbooks.Open(combinedFilename)
    Set ws2 = combinedWorkbook.Sheets("Tabular Version")
Else
    MsgBox "No file was uploaded", vbExclamation
    Exit Sub
End If

If combinedWorkbook.Worksheets(1).Range("D7") = "Periodic Insurance" Then
    ws2.Range("B10:B" & ws2.Range("B" & ws.Rows.Count).End(xlUp).Row - 1).Copy
        ws.Range("B10").PasteSpecial xlPasteValues
        ws.Range("B10").PasteSpecial xlPasteFormats
            combinedWorkbook.Close False
Else
    MsgBox "Incorrect File Selected"
    combinedWorkbook.Close False
 End If

End Sub

推荐阅读