Help with double loops please

tan21

New Member
Joined
Apr 20, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
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
 

Attachments

  • Sheet1.png
    Sheet1.png
    30.7 KB · Views: 2
  • Sheet2.png
    Sheet2.png
    8.3 KB · Views: 2
Last edited by a moderator:

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,652
Office Version
  1. 2013
Platform
  1. Windows
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

New Member
Joined
Apr 20, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Jul 5, 2014
Messages
17,652
Office Version
  1. 2013
Platform
  1. Windows
This is beyond my knowledgebase.
I will continue to monitor this thread to see what I can learn.
 

tan21

New Member
Joined
Apr 20, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Jul 5, 2014
Messages
17,652
Office Version
  1. 2013
Platform
  1. Windows
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

New Member
Joined
Apr 20, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Apr 5, 2021
Messages
937
Office Version
  1. 2010
Platform
  1. Windows

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

Watch MrExcel Video

Forum statistics

Threads
1,133,270
Messages
5,657,749
Members
418,411
Latest member
Excellency

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top