Copy and Paste to next available column

Question

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.

score 1 · Answer 1 · answered Jul 01 '15 at 23:25

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

Thank you for your reply. So far i've only used Teylyn response. If it fails, i will give yours a try. — Ryan Hubbard, Jul 02 '15 at 13:04
No problem Ryan - this is just an alternative approach. I have some slightly complicated, and repetitive macros, and found that the clipboard wasn't always a reliable tool to use. — whytheq, Jul 02 '15 at 15:29
I ended up using yours instead. You were correct about the Clipboard deal. Plus it was a little easier to adjust. For instance, i had to make sure the macro switched to the correct sheet and pasted it to the correct one. — Ryan Hubbard, Jul 02 '15 at 18:33

score 0 · Accepted Answer · edited May 23 '17 at 12:06

You can work out the column number of the last column like this:

Sub CopyPaste()
'
' CopyPaste Macro
'
Dim lastCol As Long
' this finds the number of the last column
lastCol = Cells(35, Columns.Count).End(xlToLeft).Column

   Range("G4:G33").Copy

'   Range("B35").Select
' no need to select. paste into the cell in row 35, one to the right of the last column
   Cells(35, lastCol + 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
End Sub

You could also add the 1 right in the lastCol definition, like

   lastCol = Cells(35, Columns.Count).End(xlToLeft).Column + 1
   Range("G4:G33").Copy
   Cells(35, lastCol).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
      :=False, Transpose:=False

For scheduling the macro look at these two questions here and here

I looked at the two links you posted to schedule the macro. The thing is that the page will be open every day, so no need to use a task scheduler. Think this would work? `Private Sub Workbook_Open() Application.OnTime TimeValue("11:00:00"), "MyMacro" End Sub` — Ryan Hubbard, Jul 02 '15 at 13:03

Copy and Paste to next available column

2 Answers2