Copy/paste through variable range loop

Hi,
my macro opens and copy/paste a text file into excel.
This file represents transactions per a certain group. Actually 9 groups are present. On variable ranges, "Grand Totals" per group are present. Group names are 22-1, 22-2, 22-A etc. Now, what my macro does is using the Find method (dim myval / myval = "grand") and copy / paste the found area to another sheet. So far, so good.
However, what I've done to stop the loop is to define in the 'Do until..' statement that I tell the macro to fill in range("Z1") = 1 when the "Grand total" is found of group "22-9". I rather want this to be more flexible because it may happen that from tomorrow onwards the last group will not be "22-9" but maybe "22-10" or "22-X" or whatever.
Next to this, if found a grand-total group, it will copy / paste this to another sheet but rather than going to the "select sheet2, paste values, select sheet1 and move on" procedure I would like to use the copy destination:= statement but then I will have a conflict when the macro will copy paste for the first time (since the first time there is no defined destination cell in sheet2).
Attached the file with what I made of it.
Does anyone has a better idea?
Thanks in advance for your help.

P.S. since I'm a newbie here, it would be fair that - should you think that points to be gained - you advise me if the reward correspond with your findings.

Test.xls
RepTeamAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

GrahamSkanRetiredCommented:
You could try this:
Sub test()
    Dim myval1, myval2
    Dim LastGroup As String
    Dim r As Integer
    r = Sheets("ND22").UsedRange.Rows.Count
    Do Until Sheets("ND22").Cells(r, 1).Value = "ND"
        r = r - 1
    Loop
    LastGroup = Sheets("ND22").Cells(r, 2).Value
    myval1 = "grand"
 
    Do Until Sheets("ND22 sum").Range("Z1") = 1
        Sheets("ND22").Select
        Range("A1").Select
        Cells.Find(What:=myval1, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
            :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
            False, SearchFormat:=False).Activate
            myval2 = ActiveCell.Offset(-4, -3)
        ActiveCell.Offset(1, -3).Select
        Range(ActiveCell, ActiveCell.End(xlDown).End(xlDown).End(xlDown).End(xlToRight)).Select
        Selection.Copy
        Sheets("ND22 sum").Select
        ActiveSheet.Paste
        ActiveCell = myval2
        ActiveCell.Font.Bold = True
        ActiveCell.Font.ColorIndex = 5
        ActiveCell.End(xlDown).Offset(5, 0).Select
        If myval2 = LastGroup Then
            Range("Z1") = 1
        End If
    Loop
 
 
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RepTeamAuthor Commented:
Thanks for your option. Especially the first part, finding last group, is as simple as it is effective. Overlooked this method completely, thank you.
However, I also want to get rid of the "selection.copy / sheets.... select / pastespecial etc stuff.
Any idea if this can be written more effectively like a copy destination value ?
0
GrahamSkanRetiredCommented:
I'm not too sure what you are looking for. You could get rid of most of the selection and use ranges instead. That could save some 'flickering' and a few lines of code.

Copy and paste are convenient because the whole of the cells' properties are copied, including formulae and formatting, and not just the data. However, there are other ways.
0
RepTeamAuthor Commented:
Thanks for your info and at least the first part of your solution.
With regards to the second part (copy destination other sheet), I found a similar solution, see code.
I think this does the trick and therefore accept your solution.
Thanks.
    Dim LastGroup As String
    Dim r, r1, r2
    r = Sheets("ND22").UsedRange.Rows.Count
    Do Until Left((Sheets("ND22").Cells(r, 1).Value), 2) = "ND"
        r = r - 1
    Loop
    LastGroup = Sheets("ND22").Cells(r, 1).Value
    myval1 = "grand"
    Do Until Sheets("ND22 sum").Range("Z1") = 1
    Sheets("ND22").Select
    Cells.Find(What:=myval1, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
        myval2 = ActiveCell.Offset(-4, -3)
    ActiveCell.Offset(1, -3).Select
''on the same sheet I already prepare now the lay out to be copied ''
    ActiveCell = myval2
    ActiveCell.Font.Bold = True
    ActiveCell.Font.ColorIndex = 5
'' with this 'r1' I set the last line thus preparing new spot for paste ''
    r1 = Sheets("ND22 sum").UsedRange.Rows.Count
    Range(ActiveCell, ActiveCell.End(xlToRight).End(xlDown)).Copy Destination:=Sheets("ND22 sum").Cells(r1, 1).Offset(2, 0)
    If myval2 = LastGroup Then
    Sheets("ND22 sum").Range("Z1") = 1
    End If
    Loop

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.