VBA在模板工作表和可变工作表之间复制和粘贴 [英] VBA Copy and Paste Between Template Worksheet and Variable Worksheet

问题描述

我已经编辑了这篇文章，以表明我已经做了一些研究。下面的一些代码在记录宏会话期间被捕获。我只是无法弄清楚如何将其记录的工作簿名称更改为变量。任何帮助非常感谢！

I've edited this post to show that I have done some research on this. Some of the code below was captured during a "Record Macro" session. I just can't figure out how to change the workbook names that it records into variables. Any help is greatly appreciated!

我正在尝试在两个工作表之间复制和粘贴一些数据。一个是一个模板，总是有相同的名称，另一个取决于我打开的那个。我现在的代码不断抛出一个错误。我当前的代码在下面！任何帮助将不胜感激！我想手动打开随机工作簿存储它的名称在一个变量。然后让宏打开模板文件，在templateFile中创建一个新的选项卡（称为needInfo），切换回手动打开的文件，选择范围（A1：B4），然后将其复制到templateFile中的新工作表。让我知道如果有更好的方法！我是超新的这个！

I'm trying to copy and paste some data between two worksheets. One is a template and will always have the same name and the other varies depending on which one I open. The code I have now keeps throwing up an error. My current code is below! Any help would be greatly appreciated! I would like to manualy open the random workbook store it's name in a variable. Then have the macro open the template file, create a new tab(called neededInfo) in the templateFile, switch back to the manually opened file, select range("A1:B4"), then copy that to the new sheet in the templateFile. Let me know if there is a better way! I'm super new to this!

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

推荐答案

您的代码相当接近，但有几个问题首先，不要创建其类型为变量的变量。更改 Dim WorkBook As Workbook 到类似 Dim bk As Workbook 。

Your code is fairly close but there are a few issues. First, don't create a variable with its type as the name. Change Dim workBook As Workbook to something like Dim bk As Workbook.

接下来，您有语句 Set templateFile =20161115 SMARTnet Template.xlsx。这是有效地尝试将工作簿设置为字符串。而是将其更改为设置templateFile =工作簿（20161115 SMARTnet模板）。或者更好的改为：

Next you have the statement Set templateFile = "20161115 SMARTnet Template.xlsx". This is effectively trying to set a workbook to a string. Instead change this to Set templateFile = Workbooks("20161115 SMARTnet Template"). Or better yet change this to:

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

我不完全确定你想要完成什么接下来的几行代码。看起来您正在向 templatefile 工作簿添加新的选项卡。而不是激活工作簿，只需使用这个设置tempSheet = templatefile.Worksheets.Add（之后：= ActiveSheet），然后将此新表重命名为needsInfo。

I'm not entirely sure what you're trying to accomplish with the next few lines of code. It looks like you're adding a new tab to the templatefile workbook. Instead of activating the workbook, just use this Set tempSheet = templatefile.Worksheets.Add(after:=ActiveSheet) and then rename this new sheet to "neededInfo".

接下来，您将 workingFile 变量设置为活动工作表，在这种情况下，这将是 requiredInfo 表。我假设这不是你的意图。您将需要在添加新工作表之前定义 workingSheet 变量。

Next, you set the workingFile variable to the active sheet which in this case will be the neededInfo sheet. I'm assuming that this isn't your intention. You will need to define the workingSheet variable before you add the new worksheet.

新代码可能看起来像这个：

The new code could look something like this:

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

我认为这是你的试图完成如果没有，这应该是一个很好的开始。

I think this is what you were trying to accomplish. If not, this should be a good start for you.

祝你好运！

这篇关于VBA在模板工作表和可变工作表之间复制和粘贴的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！