How do I make a variable column width in VBA code

Question

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

BigBen · Accepted Answer · 2020-11-03 20:11:50Z

2

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

answered Nov 3, 2020 at 20:11

BigBen

50.4k7 gold badges29 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

JoshL Over a year ago

I've never seen the use of Select Case, this is a real game changer. Thank you for this!

Collectives™ on Stack Overflow

How do I make a variable column width in VBA code

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related