• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 412
  • Last Modified:

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
0
Berry Metzger
Asked:
Berry Metzger
1 Solution
 
dlmilleCommented:
Attached workbook has code modifications, starting with yours.  When the macro button is run, column B, from B3 is copied (looking from bottom-up in case there's a blank row in column B) to the right-most column, starting at R3.

Here's the code in a public module:
'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()
Dim wkb As Workbook
Dim wks As Worksheet
Dim rngCopyFrom As Range
Dim rngCopyTo As Range

    Set wkb = ActiveWorkbook
    Set wks = wkb.ActiveSheet
    
    wks.Unprotect 'unprotect sheet
    
    Set rngCopyFrom = wks.Range("B3", wks.Range("B" & wks.Rows.Count).End(xlUp)) 'select entire column of values starting at B3 and looking bottom-up
    
    'get next column to the right from R3, not having data
    Set rngCopyTo = wks.Cells(3, wks.Columns.Count).End(xlToLeft).Offset(0, 1)
    If rngCopyTo.Column < Columns("R").Column Then Set rngCopyTo = wks.Cells(3, Columns("R").Column)
    
    'now copy/paste the data
    
    rngCopyFrom.Copy
    rngCopyTo.PasteSpecial xlPasteAll 'optionally, paste values, etc...
    
    Application.CutCopyMode = False
    wks.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True   ' protect sheet
End Sub

Open in new window


See attached demo workbook.  Just hit the button and see the data propogate.  Note, you have the option to paste values, rather than all, its documented in the code.  Also, there's no need to activate or select any cells - so the code will run much faster.

Enjoy!

Dave
copyPasteRoutine-r1.xlsm
0
 
Berry MetzgerLean process improvement consultantAuthor Commented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now