excel - VBA 在模板工作表和变量工作表之间复制和粘贴

Question

我编辑了这篇文章以表明我已经对此进行了一些研究。下面的一些代码是在“录制宏” session 期间捕获的。我只是不知道如何将它记录的工作簿名称更改为变量。任何帮助是极大的赞赏!

我正在尝试在两个工作表之间复制和粘贴一些数据。一个是模板，并且始终具有相同的名称，另一个取决于我打开的模板。我现在的代码不断抛出错误。我当前的代码如下!任何帮助将不胜感激!我想手动打开随机工作簿存储它在变量中的名称。然后让宏打开模板文件，在模板文件中创建一个新选项卡(称为needInfo)，切换回手动打开的文件，选择范围(“A1:B4”)，然后将其复制到模板文件中的新工作表。让我知道是否有更好的方法!我对此 super 陌生!

Sub Macro3()

Dim templateFile As Workbook ' template macro workbook; will always have same filename
Dim workingFile As String ' current working CSCC Quote download
Dim workingSheet As Worksheet ' only sheet in CSCC Quote
Dim tempSheet As Worksheet ' created sheet in CSCC Quote to hold temp data
Dim fileName As String
Dim wrkBook As Workbook
Dim nRow As Long
Dim neededInfo As Long
Dim neededRange As Range

Application.ScreenUpdating = False

' Set template file name
Set templateFile = "20161115 SMARTnet Template.xlsx"

Set templateFile = ActiveWorkbook
templateFile.Active
Set tempSheet = Worksheets.Add(after:=ActiveSheet)
tempSheet.Name = "neededInfo"
workingFile = ActiveSheet.Name
Range("A1:B4").Select
Selection.Copy
templateSheet.Activate
Range("A5").Select
ActiveSheet.Paste
workingFile.Activate

End Sub

我的解决方案:

Sub Macro3()

Dim tPlate As Workbook ' template macro
Dim wrkngFile As Workbook ' current working CSCC Quote download
Dim wrkngSheet As Worksheet ' only sheet in CSCC Quote
Dim tSheet As Worksheet ' created sheet in template file to hold temp data
Dim fileName As String
Dim tPath As String, tFile As String ' template path location and template file
Dim nRow As Long
Dim neededInfo As Long
Dim neededRange As Range

Application.ScreenUpdating = False

tPath = "C:\Users\tcoplien\Desktop\SMARTnet\"
tFile = tPath & "20161115 SMARTnet Template.xlsx"

Set wrkngFile = ActiveWorkbook
Range("A1:B4").Select
Selection.Copy

' Open template file and save name as variable
Set tPlate = Workbooks.Open(tFile)

Set tSheet = Worksheets.Add(After:=ActiveSheet)
tSheet.Name = "neededInfo"

tSheet.Range("A5").PasteSpecial xlPasteValues

wrkngFile.Activate
Range("A1").Select

End Sub

Answer 1

您的代码相当接近，但存在一些问题。首先，不要创建以类型为名称的变量。更改Dim workBook As Workbook类似于 Dim bk As Workbook .

接下来是声明 Set templateFile = "20161115 SMARTnet Template.xlsx" .这实际上是在尝试将工作簿设置为字符串。而是将其更改为 Set templateFile = Workbooks("20161115 SMARTnet Template") .或者更好的是，将其更改为:

Dim tempStr as String
tempStr = "20161115 SMARTnet Template"
Set templatefile = Workbooks(tempStr)

我不完全确定您要使用接下来的几行代码来完成什么。您似乎正在向 templatefile 添加一个新选项卡工作簿。无需激活工作簿，只需使用此 Set tempSheet = templatefile.Worksheets.Add(after:=ActiveSheet)然后将此新工作表重命名为“neededInfo”。

接下来，设置 workingFile事件工作表的变量，在本例中为 neededInfo床单。我假设这不是你的意图。您需要定义 workingSheet添加新工作表之前的变量。

新代码可能如下所示:

Sub Macro3()

    Dim templatefile As Workbook
    Dim workingSheet As Worksheet ' only sheet in CSCC Quote
    Dim tempSheet As Worksheet ' created sheet in CSCC Quote to hold temp data
    Dim tempStr As String

    Application.ScreenUpdating = False

    ' Set template file name
    tempStr = "20161115 SMARTnet Template"
    Set templatefile = Workbooks(tempStr)
    Set workingSheet = ActiveSheet
    Set tempSheet = Worksheets.Add(after:=ActiveSheet)

    tempSheet.Name = "neededInfo"

    workingSheet.Range("A1:B4").Copy

    tempSheet.Range("A5").PasteSpecial xlPasteValues

    Application.ScreenUpdating = True

End Sub

我认为这就是你想要完成的。如果没有，这对你来说应该是一个好的开始。

祝你好运!