excel - 如何在 VBA 代码中创建可变列宽

Question

我目前正在将表格从 Excel 工作表复制/粘贴到 Word 文档。我想知道是否有办法通过 vba 代码根据单元格值将表格写为变量表格。
基本上只有 If 之一下面显示的部分适用于所有值。前任:

Set Tbl = Sheet16.Range("BA17:**COLUMN#**+BZ39")
Tbl.Copy
DOC.bookmarks("Catalyst_Design_Options").Select
WRD.Selection.PasteSpecial DataType:=3, Placement:=0

我当前的代码在这里显示，它工作正常，但如果有可能使这个更清洁，那将不胜感激。

If Sheet5.Range("BN21").Value = 1 Then
Set Tbl = Sheet16.Range("BA17:BZ39")
Tbl.Copy
DOC.bookmarks("Catalyst_Design_Options").Select
WRD.Selection.PasteSpecial DataType:=3, Placement:=0

ElseIf Sheet5.Range("BN21").Value = 2 Then
Set Tbl = Sheet16.Range("BA17:CM55")
Tbl.Copy
DOC.bookmarks("Catalyst_Design_Options").Select
WRD.Selection.PasteSpecial DataType:=3, Placement:=0

ElseIf Sheet5.Range("BN21").Value = 3 Then
Set Tbl = Sheet16.Range("BA17:CW55")
Tbl.Copy
DOC.bookmarks("Catalyst_Design_Options").Select
WRD.Selection.PasteSpecial DataType:=3, Placement:=0

Else: MsgBox ("Please select a number of options from 1 to 3.")
End If

Answer 1

也许使用 Select Case :

Select Case Sheet5.Range("BN21").Value
    Case 1
        Set Tbl = Sheet16.Range("BA17:BZ39")
    Case 2
        Set Tbl = Sheet16.Range("BA17:CM55")
    Case 3
        Set Tbl = Sheet16.Range("BA17:CW55")
    Case Else
        MsgBox "Please select a number of options from 1 to 3."
        Exit Sub
End Select

Tbl.Copy
DOC.bookmarks("Catalyst_Design_Options").Select
WRD.Selection.PasteSpecial DataType:=3, Placement:=0