CODEWITHSUNDEEP
excelvba
Toxic_Turtle
Toxic_Turtle

Reputation: 21

Copy three columns of data and have them pasted in the first open columns to the right

I have a spreadsheet set up so that columns BX,BY,BZ that populate with the latest data each week. My goal is to have the latest data that is in BX, BY, BZ copied over to the first available open columns so in this case CA, CB, CC then the week after CD, CE, CF and so on so that I can have a compete historical record of how my data trends on a weekly basis. I am new to VBA and am not sure how to approach this, but have pasted what I've started below.

Any help is greatly appreciated!

Sub Copy_Latest()
'
' Copy_Latest Macro
'
' Keyboard Shortcut: Ctrl+Shift+L
'
    Columns("BX:BZ").Select
    Selection.Copy
    Columns("CC:CE").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub

Upvotes: 2

Views: 33

Answers (1)

VBasic2008
VBasic2008

Reputation: 54807

Copy Columns Adjacent to the Right of the Used Range

Option Explicit

Sub CopyLatest()
'
' CopyLatest Macro
'
' Keyboard Shortcut: Ctrl+Shift+L
'
    Const Cols As String = "BX:BZ"
    
    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
    
    With ws.UsedRange
        Dim srg As Range: Set srg = Intersect(.Cells, ws.Columns(Cols))
        Dim drg As Range: Set drg = .Resize(, srg.Columns.Count) _
            .Offset(, .Columns.Count)
        drg.Value = srg.Value
    End With
       
End Sub

Upvotes: 1

Related Questions