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.

Answer 1

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

Answer 2

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