Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Copy a range to the next blank column to the right

Posted on 2011-10-16
2
Medium Priority
?
337 Views
Last Modified: 2012-05-12
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
Comment
Question by:Berry Metzger
2 Comments
 
LVL 42

Accepted Solution

by:
dlmille earned 2000 total points
ID: 36977669
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
 

Author Closing Comment

by:Berry Metzger
ID: 36980473
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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question