Excel – Copy and Paste to next available column

excelexcel-2010vba

I'm trying to track weekly quantities I have in my spread sheet. So far I've made a macro to copy and paste the info where I need it. But it will only paste it to the spot I chose while recording the macro. I'd like it to paste the info into the next available column.

I'd also like to schedule the macro to run once a week on Friday morning.

Macro I'm using now.

Sub CopyPaste()
'
' CopyPaste Macro
'

'
   Range("G4:G33").Select
   Selection.Copy
   Range("B35").Select
   Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
End Sub

I've tried putting & lastrow into the range, but it gets a compile error. Any help would be greatly appreciated.

Best Answer

At first sight maybe slightly more complex, but in a way a more pretty way of tackling the movement of values is to avoid using the clipboard with code like this:

Sub CopyPaste()
'
' CopyPaste Macro
'
'
Dim targetRng As Excel.Range
Dim destRng As Excel.Range
Set targetRng = Range("G4:G33")

Dim lc As Long
With Excel.ThisWorkbook.Sheets("Sheet1")
    lc = .Cells(35, .Columns.Count).End(Excel.xlToLeft).Column
    Set destRng = .Range(.Cells(35, lc), .Cells(35, lc)).Offset(0, 1).Resize(targetRng.Rows.Count, targetRng.Columns.Count)
    destRng.Value = targetRng.Value
End With

End Sub

The above can be simplified to the following so you don't need to worry about using the last row variable:

Sub CopyPaste()
'
' CopyPaste Macro
'
'
Dim targetRng As Excel.Range
Dim destRng As Excel.Range
Set targetRng = Range("G4:G33")

With Excel.ThisWorkbook.Sheets("Sheet1")
    Set destRng = .Cells(35, .Columns.Count).End(Excel.xlToLeft).Offset(0, 1).Resize(targetRng.Rows.Count, targetRng.Columns.Count)
    destRng.Value = targetRng.Value
End With

End Sub
Related Topic