Resize Range

[doug.washburn]

Hi,

I have a macro that takes an active cell, resizes the selection and then copies and transposes that data onto a secon sheet.


ActiveCell.Resize(1, 28).copy
Sheets("transposed data").Select
Range("B4").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True

Now I need to use the same macro but instead of re-sizing a single cell, the user could select 1, 2, 3 or more cells in a column and I need to resize their selection across 28 columns.

Any ideas?

 

[brian.wethington]

First off some housekeeping:

ActiveCell.Resize(1, 28).copy 
Sheets("transposed data").Range("B4").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ 
False, Transpose:=True

Thus this may get what you are looking for:

Sub Test1()

Dim LstCol As Long
For Each c In Selection
    c.Resize(1, 28).Copy
    LstCol = Sheets("Transposed Data").Cells(4, Columns.Count).End(xlToLeft).Column + 1
    Sheets("transposed data").Cells(4, LstCol).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Next c

End Sub