0

I am brand new in VBA and learning on my own. I came up with a code that works but I know there must be a more elegant way to write this without using the Select method.

wscount = ActiveWorkbook.Worksheets.Count
Sheets.Add After:=Sheets(Sheets.Count)
Worksheets(1).Select
Range("B2:B100").Copy
Worksheets(wscount + 1).Select
Range("A1:A100").Select
Selection.PasteSpecial Paste:=xlPasteColumnWidths
Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
        , SkipBlanks:=False, Transpose:=False

I wish I could get the width of the column set in the code instead of copied from the source.

Also, is there a way to have the width of the column to change based on the "longest" text in one cell of the range?

Anyway, my two questions are:

  1. how to write the above code without having to use Select, and
  2. how to set the width of the column so I do not copy from the source.
YowE3K
  • 23,852
  • 7
  • 26
  • 40
Justme
  • 85
  • 1
  • 6
  • 14
  • [How to avoid using Select and Activate](http://stackoverflow.com/q/10714251/6535336). – YowE3K Mar 09 '17 at 19:53
  • For setting column widths, try recording a macro using the Macro Recorder - while it is recording set the width of a column to, for instance 10, and then also autofit the width. Stop the recorder and look at the macro generated - it will show two statements, e.g. `Columns("A:A").ColumnWidth = 10` and `Columns("A:A").EntireColumn.AutoFit`. – YowE3K Mar 09 '17 at 19:56

1 Answers1

2

There are even more elegant ways yet, but with the information provided, this will do the trick.

Dim wscount as Integer
Dim wb as Workbook
Set wb  = ActiveWorkbook
wscount = wb.Worksheets.Count

wb.Sheets.Add After:=wb.Sheets(wb.Sheets.Count)
wb.Worksheets(1).Range("B2:B100").Copy
wb.Worksheets(wscount + 1).Range("A1:A100").PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
        , SkipBlanks:=False, Transpose:=False
wb.Worksheets(wscount + 1).range("a1").EntireColumn.AutoFit
Kyle
  • 2,543
  • 2
  • 16
  • 31