Berry Metzger
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)).Se lect ' 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:=xlPasteValuesAndNum berFormats , 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
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)).Se
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:=xlPasteValuesAndNum
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I hadn't thought of accounting for blank spaces in range. I can reuse that feature elsewhere.
Thanks for your fast reply.
Berry