Pasting macro

  • Thread starter Thread starter Mark Hall
  • Start date Start date
M

Mark Hall

I am trying to find a macro that will allow me to paste data on
alternate rows. For example I want eot copy range A2:A11 and paste it
into A15, A17, A19, ... until there is a blank line above it. I am
trying to create the credit side a journal entry for an upload into our
accounting system.
 
One way:

Public Sub CopyRangeUntilCellAboveIsBlank()
Dim i As Long
For i = 1 To 10
If IsEmpty(Cells(12 + 2 * i, 1).Value) Then Exit For
Cells(i + 1, 1).Copy Cells(13 + 2 * i, 1)
Next i
End Sub
 
Do I need to specify what row to copy? Where should the cursor be when
the macro is initiated?
 
Since you didn't specify any parameters, I wrote it for your example
(i.e., A2:A10 -> A15,A17,A19). If you want it generalized, you need
to specify what steps you want to take when running the macro: Do
you want to copy a selected range to a fixed location? Copy a
variable range to a varying location?

For example, this macro will copy the selected cells to whatever
cell is specified in the inputbox (which can be filled in via
navigating with the mouse):

Public Sub CopyRangeUntilCellAboveIsBlank()
Const sPROMPT As String = "Starting cell to copy X to:"
Const sTITLE As String = "Pick Destination"
Dim cell As Range
Dim dest As Range
With Selection
On Error Resume Next
Set dest = Application.InputBox( _
Replace(sPROMPT, "X", .Address(0, 0)) & _
vbNewLine, sTITLE, Type:=8)
On Error GoTo 0
If Not dest Is Nothing Then
For Each cell In .Cells
If IsEmpty(dest.Offset(-1, 0).Value) Then Exit For
cell.Copy dest
Set dest = dest.Offset(2, 0)
Next cell
End If
End With
End Sub
 
My original question wasn't completely accurate. I want to copy data
from A2:L2, paste it beginning at A15 in every other row until the row
above where the data is pasted is blank.
 
Back
Top