HELP! Need COPY MACRO: with dialog box with yes/no to continue to next section

[hensleyj]

Hey everyone,

I am having a bit of a problem trying to get a VBA macro together.

I have a list of data in cell range: J3:J321.

I am trying to make a macro that copies the first section, J3 to J110 then A DIALOG box opens. It then checks cell L69.. if yes, it creates a dialog box and gives the options "continue" or "cancel".

If click continue, it copies J111 TO J163 and checks the next cell if "yes" or "no" if yes, repeats the same process as above. obviously with different cell numbers moving down until it reaches J321 and stops.

I have made 1 script, and it begins the copy commands, then a dialog box opens, and has a button which triggers the next macro. But this is a lot of macros...i thought there might be a more effective way to do this?

any suggestions/help would be appreciated!

 

[JLGWhiz]

Since there was limited information in the OP, this will only illustrate how it can be done. The array and copy to destination are arbitrary solely for illustration.

Sub t()
Dim rsp As Variant, ary As Variant
ary = Array("J3:J110", "J111:J163", "J164:J230") 'put range addresses in array
With ActiveSheet
    For i = LBound(ary) To UBound(ary)
        .Range(ary(i)).Copy .Cells(Rows.Count, 11).End(xlUp)(2) 'Distination is argitrary for illustration only
        rsp = MsgBox("Do you want to continue?", vbYesNo + vbQuestion, "STOP OPTION")
            If rsp = vbNo Then Exit Sub
    Next
End With
End Sub

This also assumes that the destination will be the same anchor column each copied range. It will post to the next available anchor cell.