If the problem is in using the code in a ribbon, you may want to start a new
thread and change the subject line accordingly.



SteveDB1 wrote:

hi Dave,
I have tried it just before lunch, and found a bug I need to resolve. I
remember having this bug before, and got it fixed.
I need to reboot, and once I get this resolved, or if I can't, I'll be back
and let you know the results.
Best, and thank you.
SteveB

"Dave Peterson" wrote:

So you're copying a value and pasting it 4 rows down as many times as the user
wants. And doing some formatting to that pasted cell.

I think that this does what you want:

Option Explicit
Sub ClaimN()

Dim Nu As Long
Dim i As Long
Dim Resp As Long
Dim RngToCopy As Range
Dim DestCell As Range
Dim HowManyRowsBelow As Long

HowManyRowsBelow = 4

Nu = CLng(Application.InputBox _
(prompt:="How many rows is your Abstract Sheet?", _
Title:="Number of Rows"))

If Nu < 1 Then
Exit Sub
End If

'some kind of typing check???
If Nu 50 Then
MsgBox "Too many rows down"
Exit Sub
End If

Set RngToCopy = ActiveCell 'just a single cell

Application.ScreenUpdating = False

Do
For i = 1 To Nu
Set DestCell = RngToCopy.Offset(i * HowManyRowsBelow, 0)

RngToCopy.Copy _
Destination:=DestCell

With DestCell
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.MergeCells = True
.Font.Bold = False
With .Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
End With
With .Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
End With
End With
Next i

Resp = MsgBox(prompt:="do you wish to select next column?", _
Buttons:=vbYesNo, Title:="Select Next Column")
If Resp = vbYes Then
Set RngToCopy = RngToCopy.Offset(0, 1)
Else
Exit Do
End If

Loop

'I'd do some testing before using this line!
'ActiveWorkbook.Save

Application.ScreenUpdating = True

End Sub


SteveDB1 wrote:

Morning all.
I have a macro that I want to add a continuation element to.
Presently the macro copies a cell's contents down to the end of my choosing
with an input box. I'd like to add a message box input to continue the
operation into multiple columns.
How do I call the macro to continue?
My code for the macro:
--------------------------------------------------------
Sub ClaimN() '(control As IRibbonControl) have commented out iribbon control
' for testing until it works with addition of continuation input.
'select cells with date, or claim number, and copy to
'next row used for that data.
Dim Nu As Integer
Nu = InputBox(prompt:="How many rows is your Abstract Sheet?",
Title:="Number of Rows")

Application.ScreenUpdating = False
For i = 1 To Nu

Selection.Copy

ActiveCell.Offset(rowoffset:=4, columnoffset:=0).Select

ActiveSheet.Paste
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.MergeCells = True
.Font.Bold = False

End With

With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
End With


Next i

'here starts my input message box to select next column

Dim msg 'used example out of VB help file.

msg = MsgBox("do you wish to select next column?", vbYesNo, "Select Next_
Column")
If msg = vbYes Then

'what do I place here to start the macro over again?
'I want to select one column over, and have it repeat the same as
'already performed.
'I then want to repeat this as many times as I choose.

Else
msg = vbNo
End Sub

End If

ActiveWorkbook.Save

End Sub
--------------------------------------------------

Thank you for your helps.
Best.
SteveB

--

Dave Peterson


--

Dave Peterson