VBA Excel 2010 在工作簿/工作表之间复制/粘贴，提示粘贴位置
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20414480/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA Excel 2010 Copying/Pasting Between Workbooks/sheets, prompting paste locations
提问by user3072669
I have a base spreadsheet that needs to gather data from many other spreadsheets (trials). Am trying to write a macro that accomplishes the following:
我有一个基本电子表格，需要从许多其他电子表格（试验）中收集数据。我正在尝试编写一个实现以下功能的宏：
1) Prompt open file (different file used for each macro instance)
1) 提示打开文件（每个宏实例使用不同的文件）
2) Copy column 1 from trial to a specific column in sheet 1 on base spreadsheet
2) 将第 1 列从试验复制到基本电子表格上第 1 表中的特定列
3) Copy column 2 from trial to a specific column in sheet 2 on base spreadsheet, and so on..
3) 将第 2 列从试验复制到基本电子表格上第 2 表中的特定列，依此类推。
4) While for each file the column that is being copied to is different (ie, trial 1 might be copied to Y2:Y102, trial 3 might be A2:A102), it will be the same range in each sheet
4）虽然对于每个文件，被复制到的列是不同的（即，试验 1 可能被复制到 Y2:Y102，试验 3 可能是 A2:A102），但它在每张表中的范围都相同
So what I want from the macro is for it to prompt me once for the range for one sheet, then continue to copy the columns into the same range but in different sheets without prompting.
所以我想要从宏中提示我一次为一张工作表的范围，然后继续将列复制到相同的范围但在不同的工作表中而不提示。
Here is what I have so far:
这是我到目前为止所拥有的：
Sub skymacroT1()
TrialFile = Application.GetOpenFilename
Workbooks.Open TrialFile
Range("B4:B104").Select
Selection.Copy
'
Windows("Sky Braced Pressure Combined.xlsm").Activate
Sheets("Lt Heel").Select
Dim MyRange As Range
Set MyRange = Application.InputBox("select cell", Type:=8)
MyRange.Select
Range("MyRange").Paste
Application.CutCopyMode = False
'
Windows("TrialFile").Activate
Range("C4:C104").Select
Selection.Copy
'
Windows("Sky Braced Pressure Combined.xlsm").Activate
Sheets("Lt Met").Select
MyRange.Select
Range("MyRange").PasteSpecial
Application.CutCopyMode = False
End Sub
However the code fails in two spots: Range("MyRange").Pastefails to paste and Windows("TrialFile").Activatedoesn't open the window for the file I prompted for.
但是，代码在两个地方Range("MyRange").Paste失败：无法粘贴并且Windows("TrialFile").Activate没有打开我提示输入的文件的窗口。
Any help would be greatly appreciated!!
任何帮助将不胜感激！！
回答by L42
This Range("MyRange").Pastefails because Myrangeis not a Named Rangebut rather a variable object of the Rangetype. It should be like this:
这Range("MyRange").Paste失败了，因为Myrange它不是类型Named Range的变量对象，而是Range类型的变量对象。应该是这样的：
Myrange.Paste
This Windows("TrialFile").Activatedoesn't work because TrialFilecontains the full pathof the Workbook. You only need the Workbook nameto activate it using Windows. So it should be like this:
这Windows("TrialFile").Activate不起作用，因为TrialFile包含full path的Workbook。您只需要使用Workbook name来激活它Windows。所以它应该是这样的：
Workbooks.Open TrialFile
TrialFilename = Activeworkbook.Name
'~~> other code goes here
Windows(TrialFilename).Activate
I've also re-written your code below:
我还在下面重写了你的代码：
Option Explicit '~~> Force Variable Declaration
Sub skymacroT1()
'~~ Declare all variables
Dim TrialFile, wbTrial as Workbook, wbSky as Workbook
Dim MyRange as Range, MyRangeAdd as String
TrialFile = Application.GetOpenFilename
Set wbTrial = Workbooks.Open(TrialFile)
Set wbSky = Workbooks("Sky Braced Pressure Combined")
'~~> Can't be ommitted since you need to see the first address in the destination sheet to copy to.
wbSky.Sheets("Lt Heel").Activate
'~~> Identify the objects first before you work on them
Set MyRange = Application.InputBox("select cell", Type:=8)
MyRangeAdd = MyRange.Address '~~> set the universal address for all sheets
'~~ Proceed with copying
wbTrial.Sheets(1).Range("B4:B104").Copy wbSky.Sheets("Lt Heel").Range(MyRangeAdd)
wbTrial.Sheets(1).Range("C4:C104").Copy wbSky.Sheets("Lt Met").Range(MyRangeAdd)
End Sub
The above code is just modified based on how you want to structure your procedure.
I'd like to eliminate the Activatemethod all in all but i think you will need to let the user see the target range first so it can't be helped.
上面的代码只是根据您希望如何构建您的过程进行了修改。
我想完全消除该Activate方法，但我认为您需要先让用户看到目标范围，因此无济于事。
回答by sam092
Quick answer
快速回答
MyRangeand TrialFileare variables.
MyRange并且TrialFile是变量。
When you refer to them, you don't need to enclose it with double quotes.
当您引用它们时，您不需要用双引号将其括起来。
