Link to home
Start Free TrialLog in
Avatar of Berry Metzger
Berry MetzgerFlag for United States of America

asked on

Copy a range to the next blank column to the right

I need a macro that copies a range of [changing] values linked to other sheets, say, range B3:B20, to another range beginning at R3, if empty and for the first copy iteration.  Then copies B3:B20 to S3 for the second iteration so as not to overwrite previous copy iterations, then to T3..., etc., etc.
Code [needing capability for progressive copying to a next empty column to the right] follows:

Sub Copy_To_Trend_Visual_Aid  
 ActiveSheet.Unprotect    ' unprotect sheet
 Range("B3").Select      ' start at cell B3
 Range(ActiveCell, ActiveCell.End(xlDown)).Select  ' select range of contiguous rows
 Selection.Copy                              ' copy the range, say, B3:B20
 Range("R3").Select                       ' go to cell R3 [the first blank column to begin successive  copy iterations]

' { code needed here: move cursor to R3 and pastes range B3:B20.  If R3 is not blank [already used/occupied], select the next-to-the-right empty cell from R3 and copy to, say, S3 [or any next blank cell to the right]. On successive copy/paste iterations seeks next unused /adjacent cell to the right, e.g., T3, U3, V3 ..., etc. }
 
 Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False    ' pastes values & CF
 Range("a1").Select                                                       ' park cursor in standard location
 Application.CutCopyMode = False
 ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True   ' protect sheet
End Sub
ASKER CERTIFIED SOLUTION
Avatar of dlmille
dlmille
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Berry Metzger

ASKER

Your solution exceeds expectations Dave!
I hadn't thought of accounting for blank spaces in range.  I can reuse that feature elsewhere.
Thanks for your fast reply.
Berry