时间戳

首页 > 解决方案 > 设置将要粘贴范围的输入框

excel - 设置将要粘贴范围的输入框

问题描述

我在 YouTube 上观看了这个视频，它真的对我帮助很大，现在我必须改进该代码。所以在代码中粘贴值的行在哪里，我必须设置输入框或其他东西，让用户选择他想要粘贴的位置。这是我的代码：

Sub IMPORT_DATA()
Dim FileToOpen As Variant
Dim OpenBook As Workbook
Application.ScreenUpdating = False
    FileToOpen = Application.GetOpenFilename(Title:="Browse for your File & Import Range", FileFilter:="Excel Files(*.xlsx),*xlsx")
    If FileToOpen <> False Then
        Set OpenBook = Application.Workbooks.Open(FileToOpen)
        OpenBook.Sheets("NELT report").Range("R7:R14").Copy
        ThisWorkbook.Worksheets("Dispatch Monthly NETO").Range("L5").PasteSpecial xlPasteValues
        OpenBook.Close False
        Range("L5:L12").Interior.Color = RGB(255, 242, 204)
    End If
    Application.ScreenUpdating = True
End Sub

标签： excelvbainputbox

解决方案

Sub IMPORT_DATA()
Dim FileToOpen As Variant
Dim OpenBook As Workbook
Dim s As String ' Range to Paste
Application.ScreenUpdating = False
FileToOpen = Application.GetOpenFilename(Title:="Browse for your File & Import 
Range", FileFilter:="Excel Files(*.xlsx),*xlsx")
If FileToOpen <> False Then
Set OpenBook = Application.Workbooks.Open(FileToOpen)
OpenBook.Sheets("NELT report").Range("R7:R14").Copy
s = InputBox("Range to Paste : ")
If s = "" Then Exit Sub
ThisWorkbook.Worksheets("Dispatch Monthly NETO").Range(s).PasteSpecial 
xlPasteValues
OpenBook.Close False
Range("L5:L12").Interior.Color = RGB(255, 242, 204)
End If
Application.ScreenUpdating = True
End Sub

推荐阅读