vba 从另一张工作表自动填充一张工作表中的列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17816585/
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
Auto populate columns in one sheet from another sheet
提问by Kyle Copeland
I would like to populate columns in sheet2 from sheet1. If I have column Ain Sheet1I want Ain Sheet2to have the same information.
我想从 sheet1 填充 sheet2 中的列。如果我有列A在Sheet1我希望A在Sheet2具有相同的信息。
I tried using =sheet1!A1but it only returns the value from A1in sheet1. I tried using =sheet1!Abut it only returns #NAME?.
我尝试使用，=sheet1!A1但它只返回A1in 中的值sheet1。我尝试使用=sheet1!A但它只返回#NAME?.
If Column Afrom Sheet1has a dynamic range (it can be empty or have 500 or 1000 rows (I'm populating sheet1 from my database)). How do I use some of those columns in another sheet showing all 500 or 1000 rows?
如果 Column AfromSheet1具有动态范围（它可以为空或有 500 或 1000 行（我正在从我的数据库填充 sheet1））。如何在显示所有 500 或 1000 行的另一张工作表中使用其中一些列？
采纳答案by Santosh
Below code will look for last used row in sheet1 and copy the entire range from A1 upto last used row in column A to Sheet2 at exact same location.
下面的代码将在 sheet1 中查找最后使用的行，并将从 A1 到 A 列中最后使用的行的整个范围复制到 Sheet2 完全相同的位置。
Sub test()
Dim lastRow As Long
lastRow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
Sheets("Sheet2").Range("A1:A" & lastRow).Value = Sheets("Sheet1").Range("A1:A" & lastRow).Value
End Sub
回答by Denis
If I understood you right you want to have sheet1!A1 in sheet2!A1, sheet1!A2 in sheet2!A2,...right?
如果我理解正确，您希望在 sheet2!A1 中有 sheet1!A1，在 sheet2!A2 中有 sheet1!A2，...对吗？
It might not be the best way but you may type the following
这可能不是最好的方法，但您可以输入以下内容
=IF(sheet1!A1<>"",sheet1!A1,"")
=IF(sheet1!A1<>"",sheet1!A1,"")
and drag it down to the maximum number of rows you expect.
并将其向下拖动到您期望的最大行数。
回答by Cholavendhan
I have used in Google Sheets
我在谷歌表格中使用过
={sheetname!columnnamefrom:columnnameto}
Example:
例子：
={sheet1!A:A}={sheet2!A4:A20}
={sheet1!A:A}={sheet2!A4:A20}
回答by Meesh
In Google Sheets you can use =ArrayFormula(Sheet1!B2:B)on the first cell and it will populate all column contents not sure if that will work in excel
在 Google 表格中，您可以在第一个单元格上使用 =ArrayFormula(Sheet1!B2:B) ，它将填充所有列内容，不确定这是否适用于 excel
回答by Jahmic
Use the 'EntireColumn' property, that's what it is there for. C# snippet, but should give you a good indication of how to do this:
使用“EntireColumn”属性，这就是它的用途。C# 代码段，但应该可以很好地说明如何执行此操作：
string rangeQuery = "A1:A1";
Range range = workSheet.get_Range(rangeQuery, Type.Missing);
range = range.EntireColumn;
