VBA Excel 如何插入预定行的标题

VBA Excel How to insert a predetermined row of headers

提问by Scott T

I'm having a hard time looking up what I am trying to do because I am having a hard time putting what I am trying to do into words.

我很难查找我正在尝试做的事情，因为我很难将我想做的事情用语言表达出来。

Basically, what I am trying to do is take a set of headers (maybe 10-12 columns long) and, in a macro, I want to paste those headers over (replace) the current headers in row 1 of sheet 1. The way I currently have things set up is that my data is being inserted starting at row 2 of every sheet when that sheet is generated. I have a macro written that will copy row 1 of sheet 1 into every existing sheet, however, I need to not have to manually insert the first sheet's headers (it should be part of the macro).

基本上，我想要做的是获取一组标题（可能长 10-12 列），并且在宏中，我想将这些标题粘贴到（替换）第 1 页第 1 行中的当前标题上。方式我目前的设置是在生成该工作表时，我的数据从每张工作表的第 2 行开始插入。我编写了一个宏，可以将工作表 1 的第 1 行复制到每个现有工作表中，但是，我不必手动插入第一张工作表的标题（它应该是宏的一部分）。

So I guess I need assistance in how to insert a row of predetermined headers over row 1 of sheet 1 ( I want to write the actual header names into the macro code).

所以我想我需要关于如何在第 1 页的第 1 行插入一行预定标题的帮助（我想将实际的标题名称写入宏代码）。

EDIT:

编辑：

Application.CutCopyMode = True

Dim Counter As Long

Counter = Sheets.Count
For i = 1 To Counter
    Sheets("Sheet1").Cells(1, 1).EntireRow.Copy
    Sheets(i).Cells(1, 1).PasteSpecial

Next i

Application.CutCopyMode = False

This what I have for copying code across and this works fine. What I want is to be able to put in a line of code that basically says 'Paste "Header 1" "Header 2" "Header 3" etc' into the corresponding columns of row 1 sheet 1. So I have my header names already picked out but I want to put them directly into the macro code. So if my headers are Apply Banana Lettuce Tomato, I want to put those words into the macro code and have them pasted in sheet 1 row 1 before my copy-paste code listed above.

这是我用来复制代码的东西，而且效果很好。我想要的是能够放入一行代码，基本上说“将“标题1”“标题2”“标题3”等“粘贴到第1行表1的相应列中。所以我已经有了我的标题名称挑出来，但我想把它们直接放到宏代码中。因此，如果我的标题是 Apply Banana Lettuce Tomato，我想将这些词放入宏代码中，并在上面列出的复制粘贴代码之前将它们粘贴到工作表 1 的第 1 行中。

回答by BruceWayne

Since you know your headers ahead of time, you can switch them out with mine below. This should take the headers you enter, and will paste them into all sheets. NOTE: This will also REMOVE any information in row 1 on any sheet, so if you don't want that to happen, comment/delete the line.

因为你提前知道你的标题，你可以在下面用我的把它们切换出来。这应该采用您输入的标题，并将它们粘贴到所有工作表中。注意：这也将删除任何工作表上第 1 行中的任何信息，因此如果您不希望发生这种情况，请注释/删除该行。

Sub AddHeaders()
Dim headers() As Variant
Dim ws As Worksheet
Dim wb As Workbook

Application.ScreenUpdating = False 'turn this off for the macro to run a little faster

Set wb = ActiveWorkbook

headers() = Array("Superhero", "City", "State", "Country", "Publisher", "Demographics", _
    "Planet", "Flying Abilities", "Vehicle", "Sidekick", "Powers")
For Each ws In wb.Sheets
    With ws
    .Rows(1).Value = "" 'This will clear out row 1
    For i = LBound(headers()) To UBound(headers())
        .Cells(1, 1 + i).Value = headers(i)
    Next i
    .Rows(1).Font.Bold = True
    End With
Next ws

Application.ScreenUpdating = True 'turn it back on

MsgBox ("Done!")

End Sub

You can also use that as a general macro - it prevents needing copy/paste (which is a good thing to learn in VBA, to avoid copy/paste), and you can also create a dynamic header array, if your headers change often. Let me know if this is on track for what you're looking for!

您也可以将其用作通用宏 - 它可以防止需要复制/粘贴（这是在 VBA 中学习的一件好事，以避免复制/粘贴），并且如果您的标头经常更改，您还可以创建动态标头数组。让我知道这是否符合您的要求！

Edit: Note about arrays. By default, an array that has 5 items will be indexed from 0 to 4. I.e. If you do this loop (psuedocode):

编辑：关于数组的注意事项。默认情况下，具有 5 个项目的数组将从 0 到 4 进行索引。即，如果您执行此循环（伪代码）：

myArray() = Array("Batman","Superman","Catwoman","Rorschach")
for i = 1 to uBound(myArray())
  debug.print myArray(i)
Next i

It will only print "Superman","Catwoman","Rorschach". This is because "Batman", as the first entry, is the 0 index. Simply replace the forline with this one for i = lBound(myArray()) to uBound(myArray())to go from the first to last entry. Or, you can use for i = 0 to uBound(...

它只会打印“超人”、“猫女”、“罗夏”。这是因为“蝙蝠侠”作为第一个条目，是 0 索引。只需将此for行替换为for i = lBound(myArray()) to uBound(myArray())从第一个条目到最后一个条目。或者，您可以使用for i = 0 to uBound(...

But you'll notice in that loop, I have .Cells(1, 1+i).... This is because my loop is starting at 0, so I had to add 1 to i so it would start in the second column.

但是你会注意到在那个循环中，我有.Cells(1, 1+i).... 这是因为我的循环从 0 开始，所以我必须将 1 添加到 i 以便它从第二列开始。

Don't like that Zero-based array (where it starts at 0)? You can start at 1! How? Just do Dim myArray(1 to 10). This will create an array, but starting at 1. Then, this example will print all four people:

不喜欢那个从零开始的数组（从 0 开始）？你可以从1开始！如何？就做Dim myArray(1 to 10)。这将创建一个数组，但从 1 开始。然后，此示例将打印所有四个人：

for i = 1 to uBound(myArray())
    debug.print myArray(i)
next i

One tip, for looping through an entire array, is to just use lBound(array())and uBound(array())which is the first value, and last value, respectively. This will also allow you to do .Cells(1,i).Value ..., since i starts at 1. Does this make sense?

循环遍历整个数组的一个技巧是只使用lBound(array())anduBound(array())分别是第一个值和最后一个值。这也将允许你做.Cells(1,i).Value ...，因为我从 1 开始。这有意义吗？

回答by 3-14159265358979323846264

Assuming your headers are in columns A1:J1on Sheet1, you can use the following to copy them into B1:K1on Sheet2

假设您的标题位于A1:J1on列中Sheet1，您可以使用以下内容将它们复制到B1:K1onSheet2

Sheets("Sheet1").Range("A1:J1").Copy Sheets("Sheet2").Range("B1:K1")

回答by T.M.

How to write sheet headers by one code line only

如何仅通过一行代码编写工作表标题

Instead of loopingthrough each title in the headersarray, one can write the whole array to a given cell start (e.g. A1) via

不是遍历headers数组中的每个标题，而是可以通过以下方式将整个数组写入给定的单元格开头（例如A1）

    ws.Range("A1").Resize(1, UBound(headers) + 1) = headers 

where the target range will be resized to 1 row and the correct column count via the UBoundfunction; as headersis a zero-boundarray, just add +1to get all titles.

其中目标范围将通过函数将d调整为 1 行和正确的列数UBound；作为headers一个零边界数组，只需添加+1即可获取所有标题。

Sub WriteHeaders()
Dim headers(), ws As Worksheet
headers() = Array("Superhero", "City", "State", "Country", "Publisher", "Demographics", _
                  "Planet", "Flying Abilities", "Vehicle", "Sidekick", "Powers")
For Each ws In ThisWorkbook.Worksheets
    .Rows(1) = vbNullString                                 ' clear header row
    .Range("A1").Resize(1, UBound(headers) + 1) = headers   ' write headers into row 1
Next ws
End Sub

Some coding variations (edited due to questions in comment thx Bruce)

一些编码变体（由于评论中的问题 thx Bruce 进行了编辑）

Note:I changed your questions for systematic reasons, but tried to include all.

注意：我出于系统原因更改了您的问题，但尝试包括所有问题。

Do you get an error if say headers() was five items, and I did .Range("A1:B1") = headers?

如果说 headers() 是五个项目，你会得到一个错误.Range("A1:B1") = headers吗？

• No, you don't, it just fills the first two items from headersinto the indicated cells. So you can use such a construction to reduce a predefined full set of elements to a given number of cells without redimming the (1-dim) array. Personally I prefer resizing as demonstrated, e.g. via .Range("A1").Resize(1,2) = headers.

• 不，您不需要，它只是将前两项填充headers到指定的单元格中。因此，您可以使用这样的构造将预定义的完整元素集减少到给定数量的单元格，而无需重新调整 (1-dim) 数组。就我个人而言，我更喜欢按照演示调整大小，例如通过.Range("A1").Resize(1,2) = headers.

Fill a vertical target range

填充垂直目标范围

• If on the other hand you code .Range("A1:A2") = headersyou have to keep in mind, that this is a flat(1-dimensioned) array which you want to force into a column range - you'll get only the first identical header item in bothcells.
• To make this work for the available headers here, you'd have to transpose the flat array to a vertical one as follows: .Range("A1:A2") = Application.Transpose(headers)and you'll get at least two of five array items.

• 另一方面，如果您编写代码，.Range("A1:A2") = headers您必须记住，这是一个平面（一维）数组，您希望将其强制放入列范围内 - 您将仅在两个单元格中获得第一个相同的标题项。
• 为了使这里的可用标题起作用，您必须按如下方式将平面数组转换为垂直数组：.Range("A1:A2") = Application.Transpose(headers)并且您将至少获得五个数组项中的两个。

Likewise, if I did .Range("A1:AA1") = headerswould it a) just fill in the first five items and b) leave the rest blank?

同样，如果我这样做了，.Range("A1:AA1") = headers会不会 a) 只填写前五项，b) 将其余部分留空？

• a) Yes, as in the example above, any available item gets filled in,

• b) no, the cells after complete Input of headers is filled by error #N/A(not available) entries.

• Filling a vertical range would need to transpose as well, as by Range"A1:A100") = Application.Transpose(headers)getting the same #N/Aentries after five header items.

• a)是的，就像上面的例子一样，任何可用的项目都会被填写，

• b)否，标题完成输入后的单元格由错误#N/A（不可用）条目填充。

• 填充垂直范围也需要转置，就像在五个标题项之后Range"A1:A100") = Application.Transpose(headers)获取相同的#N/A条目一样。

回答by Uri Goren

In order to copy the header from Sheet1to all other sheets automatically, use:

为了自动将标题复制Sheet1到所有其他工作表，请使用：

srcSheet = "Sheet1"
For dst = 1 To Sheets.Count
    If Sheets(dst).Name <> srcSheet Then
    Sheets(srcSheet).Rows("1:1").Copy
    Sheets(dst).Range("A1").Select
    ActiveSheet.Paste
    End If
Next

This code iterates through all the sheets, except srcSheetand copies the first row from srcSheetto them

此代码遍历所有工作表，除了srcSheet并将第一行复制srcSheet到它们