vba 用于从一个 Excel 文件复制到另一个 Excel 文件的宏
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15389295/
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
Macro for copying from one excel file into another
提问by user2166059
I'm new to using macros and need some help copying rows from one excel file and pasting into another as columns. I've done the first row manually, while recording the macro and here is the code:
我是使用宏的新手，需要一些帮助从一个 excel 文件复制行并将其作为列粘贴到另一个文件中。我已经手动完成了第一行，同时录制了宏，这是代码：
Sub Macro2()
Macro2 Macro
Range("D5:L5").Select
Selection.Copy
Windows("New_SET_Data.xlsx").Activate
Range("B7").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Windows("Original_SET_Data.xls").Activate
End Sub
This needs to be done for multiple rows, and worksheets within the original excel file.
这需要为原始 excel 文件中的多行和工作表完成。
回答by MattCrum
Put your ranges, worksheets & workbooks in as variables, then it's easier to work out. Use the WorksheetFunction "Transpose" to change the data - something like this:
将您的范围、工作表和工作簿作为变量放入，这样计算起来会更容易。使用 WorksheetFunction "Transpose" 来改变数据——像这样：
Sub Macro2()
Dim wb1 as Workbook, wb2 as Workbook
Dim ws1 as Worksheet, ws2 as Worksheet
Dim rngSource as Range, Dim rngDest as Range, rngTemp as Range
Dim varArray() as Variant
Set wb1 = Workbooks("Original_SET_Data.xls")
Set wb2 = Workbooks("New_SET_Data.xlsx")
'To work through all sheets in Original_Set_Data.xls, you can replace this line with a "For each ws1 in wb1.Worksheets" loop and put Next at the end.
Set ws1 = wb1.Worksheets("Whatever_The_Source_Sheet_Is_Called")
Set ws2 = wb2.Worksheets("Whatever_The_Destination_Sheet_Is_Called")
'Find the Source Range & Next Available Destination Column
Set rngSource = ws1.Range("D5", ws1.Range("D60000").end(xlUp).Address)
Set rngDest = ws2.Range("IV7").End(xlToLeft).Offset(0,1)
'Loop through Source Range and Transpose Data
For each rngTemp in rngSource
'Put values from columns D to L into an array
varArray = ws1.Range(rngTemp, rngTemp.Offset(0, 8).Value)
'Transpose data - there are 9 columns D to L so we resize the range to be 9 rows high
rngDest.Resize(9,1).Value = WorksheetFunction.Transpose(varArray)
'Move to next column in new sheet
Set rngDest = rngDest.Offset(0,1)
Next
End Sub
