Excel VBA-将带有文本和格式的新列添加到多个工作表 [英] Excel VBA - Add new columns with text and formatting to multiple worksheets

问题描述

我正在处理一个Excel宏(按钮)，它将在多个工作表的同一位置添加一列.另外，此列必须具有通过对话框输入的列标题.

I am working on an Excel macro (button) that will add a column into the same spot in multiple worksheets. Also, this column must have a column header that is input through a dialog box.

每个工作表都包含一个格式完全相同的表-唯一的区别是工作表名称.

Each worksheet contains a table formatted the exact same way - the only difference is the worksheet names.

这是我到目前为止所拥有的:

Here is what I have so far:

Sub CommandButton2_Click()

Sheets(Array("Sheet1", "Sheet2")).Select
Sheets("Sheet2").Activate
Columns("F:F").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromRightOrBelow

Dim myValue As Variant

myValue = InputBox("Enter Thought Leadership Title", "New Thought    Leadership", "XXXXX")

Sheets(Array("Sheet1", "Sheet2")).Select
Range("F5").Value = myValue

End Sub

这确实使我真正想要'Sheet1'-添加一列(F)，并将通过对话框指定的标题名称输入到单元格F5中，同时将格式立即从该列复制到右侧.但是，它对Sheet2所做的所有操作(以及所有其他操作，为简便起见都已删除)都添加到了列中，而没有从右侧复制格式或添加通过对话框指定的文本.

This has gotten me exactly what I want for 'Sheet1' - adds a column (F) and inputs a header name specified through the dialog box into cell F5 while copying the formatting from the column immediately to the right. However, all it does for Sheet2 (and all others, but removed them for brevity) is add in a column without copying formatting from the right or adding the text specified through the dialog box.

初学者VBA程序员"，并通过使用此处的论坛获得了这一点.

Beginner VBA "programmer" and have gotten this far through using the forums here.

希望我很清楚-感谢您的输入.

Hopefully I was clear - thanks for any input.

推荐答案

@ThomasInzina的替代方法是:

An alternative to @ThomasInzina is this:

Sub commandButton2_Click_Test()
Dim myValue As Variant

For Each Worksheet In ActiveWorkbook.Worksheets
    With Worksheet
        .Range("F:F").EntireColumn.Insert shift:=xlToRight, copyOrigin:=xlFormatFromRightOrBelow
        myValue = InputBox("Enter Thought Leadership Title", "New Thought    Leadership", "XXXXX")

    End With
Next Worksheet
Worksheets("Sheet1").Range("F5").Value = myValue ' I'm not sure where you wanted this,
Worksheets("Sheet2").Range("F5").Value = myvalue ' or what it does, so tweak as necessary.
End Sub

我尝试使其尽可能与您的代码相似，同时避免使用.Select(如我在OP下方的评论中所述).

I tried to keep it as similar to your code as possible, while avoiding .Select (as mentioned in my comment below OP).

注意:这将在工作簿中的所有所有工作表中循环.您可以添加If worksheet.name = "Sheet1" or worksheet.name = "Sheet2"行，使其仅在这些行上运行.

Note: This will loop through all worksheets in your workbook. You can add a line If worksheet.name = "Sheet1" or worksheet.name = "Sheet2" to only run it on those.

这篇关于Excel VBA-将带有文本和格式的新列添加到多个工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！