0
votes

I have a range of data - B5:AG1004

In the macro, I need to copy this range and paste it in the next available column. The dedicated space for pasting begins in AX5.

In the code I have now, it copies and pastes the range into the desired (first) position, however once I click the command button again it re-pastes into the exact same place i.e. overwriting the original paste. I need the next iteration to paste in the next available cells to the right.

Here is my code so far;

Sub columnmacro()

  ActiveSheet.Range("B5:AG1004").Copy
  Sheets("Optimise").Range("ax5").End(xlToLeft).Offset(, 1).PasteSpecial xlValues
  Application.CutCopyMode = False

End Sub

Hopefully someone can help, thanks!

1
Is there guaranteed to be something in the first row of what you paste?eirikdaude

1 Answers

0
votes

Try this:

Sub SubColumnMacro()
    
    'Declarations.
    Dim RngSource As Range
    Dim RngDestination As Range
    
    'Setting variables.
    Set RngSource = ActiveSheet.Range("B5:AG1004")
    Set RngDestination = Sheets("Optimise").Range("AX5").Resize(RngSource.Rows.Count, RngSource.Columns.Count)
    
    'Finding the next avaiable spot on the right to report RngSource values.
    Do Until Excel.WorksheetFunction.CountBlank(RngDestination) = RngDestination.Cells.Count
        Set RngDestination = RngDestination.Offset(0, 1)
    Loop
    
    'Reporting Rngsource values in RngDestination.
    RngDestination.Value = RngSource.Value
    
End Sub