How do I make a variable column width in VBA code

user_13039650 0
Viewed 99

I am currently copying/pasting a table from an Excel sheet to a word document. I was wondering if there is a way through the vba code to write the table as a variable table depending on a cells value.

Basically just have one of the If sections shown below that works for all values. EX:

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

My current code is shown here which works fine but if it were possible to make this cleaner it would be much appreciated.

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
vba excel
1 Answers

Perhaps using 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
user_92458530
Related