Help with double loops please

[tan21]

First time poster here
I have an outer loop which copies rows (A11:G11) to rows (A67:G67) into row (A6:G6) one row at a time in Sheet1. Everytime the next row is copied into A6:G6, it should replace the previous copied row. For each row that is copied, there is an inner loop which should copy cells B6 to F6 into cell A6 in Sheet2 one cell at a time.
So when A11:G11 is copied into A6:G6, cells B6 to F6 from sheet1 is copied one cell at a time into cell A6 in sheet2 and the first inner loop is complete. The program must now return back to sheet1 and copy A12:G12 into A6:G6, then copy cells B6 to F6 from sheet1 to A6 in sheet2 one cell at a time and the process repeats itself until A67:G67.
I have made the following script but it doesn't work any further than for A11:G11 and remains in the wrong sheet.
I am very new to VBA code and hope someone can fix it for me.

Sub Copy_in_loop()

Dim x, y As Integer 'set variable for numbers of Rows, Columns to deal

    ' Set numrows = number of rows of data starts from A11 till last
    NumRows = Worksheets("Sheet1").Range("A11", Range("A11").End(xlDown)).Rows.Count
                
    ' Select first cell to start with
    Worksheets("Sheet1").Range("A11:G11").Select
     
    ' Establish "For" loop to loop "numrows" number of times
    For x = 1 To NumRows
     
    'to copy active cell to range("A6:G6") Override values
    ActiveCell.Range("A1:G1").Copy Range("A6:G6")
                  
    ' Select the first Activity from the first process chain
    Worksheets("Sheet1").Range("B6").Select
           
            'Establish "For" loop to loop 1 -5
            For y = 1 To 5
               
            'to copy active cell to range A6 in Worksheet Activity Database
            ActiveCell.Range("B1").Copy Worksheets("Sheet2").Range("A6")
           
            'Selects cell left 1 column to active cell.
            ActiveCell.Offset(0, 1).Select
           
            Next
           
    ' Selects cell down 1 row from active cell.
    ActiveCell.Offset(1, 0).Select
       
    Next

End Sub

 

[My Aswer Is This]

I may be able to help if you would explain what the objective here is.
Your just telling us how you want the script to run.

Tell me the objective and I will write the script the way I want.

 

[tan21]

I may be able to help if you would explain what the objective here is.
Your just telling us how you want the script to run.

Tell me the objective and I will write the script the way I want.

Thank you for your reply. The objective is to copy one row at a time into A6:G6 in sheet1. Each time a row is copied in A6:G6, it triggers a calculation in another sheet (I have already set IF statements in the calculation sheets). At the same time, the second objective is to copy one cell at a time from B6 to F6 into A6 of sheet2. The second objective is repeated for every row that is copied into A6:G6 in sheet1. As the cells are copied one by one it also triggers some further calculations in another sheet.
The aim is to automate this copy paste process as there is a large number of rows in sheet1 with each row having at least 4 cells to copy into sheet2.

 

[My Aswer Is This]

This is beyond my knowledgebase.
I will continue to monitor this thread to see what I can learn.

 

[tan21]

This is beyond my knowledgebase.
I will continue to monitor this thread to see what I can learn.

I am not very strong with loops. If i remove my inner loop, I am able to cycle through the rows and copy them one by one into A6:G6 within the sheet1 without any problems. After adding the inner loop, I can cycle through B6:F6 one by one and copy into the A6 in sheet2. But I cannot go back into sheet1 and make the outer loop go to row 2 and copy/paste it into A6:G6 in sheet1 after the inner loop is complete.

 

[My Aswer Is This]

I just have never seen a request like this.
Looping around and around expecting formulas to create some results is not something I have ever seen.

I would think vba formulas could provide a answer without all this looping.

 

[tan21]

I just have never seen a request like this.
Looping around and around expecting formulas to create some results is not something I have ever seen.

I would think vba formulas could provide a answer without all this looping.

The results are not very complex, there are IF statements and depending on the numbers that are copied into A6:G6 in sheet1 and A6 in sheet2, it produces an individual record in the result sheet. Manually copy pasting numbers do that perfectly. But the number of rows in sheet1 is dynamic and can have more rows than that which is feasible to manually copy/paste.
Hence I am looking for an automated way to sequentially copy/paste.

 

[Marc L]

As it can - must for efficiency - be achieved just evaluating directly the result rather than copying anything …​