Copy Paste in loop in loop

manojsaxena 3 Posts Sunday March 11, 2018Registration date March 13, 2018 Last seen - Mar 12, 2018 at 01:15 AM - Latest reply: ac3mark 9622 Posts Monday June 3, 2013Registration dateModeratorStatus June 22, 2018 Last seen
- Mar 14, 2018 at 03:35 PM
Hi Trowa,

I am new to Macro - but i have similar request to copy paste in 2 loops
1. copy a number times Sheet 1 Column 1 data ( say having 3 values but may vary) based on a value column B( which is a value derived from counta function) - to Sheet 2 column D starting row no 3 till end of row, then before moving to the next value form sheet 1 A, i want another action
2.Copy Sheet 2 data ( Starting from C2::E & to end of rows) to Sheet 3 ( Starting from C2: E& to end of rows)
3. Loop back to Sheet 1 data and restart the above 2 process - so that sheet 2 column D data is refreshed with new data and copy this new data ( Sheet 2 C2::E) appended to sheet 3 to have all multiplied values of sheet 1 and 2 pasted in Sheet 3

Sheet 1 data:
Column A | B
Table1 | B1 =Counta(Sheet2 -range C2:C200)
Table2|
Table 3|

Sheet 2 Data

Column |C|D|E| startign from Row 2
Delhi|Table1( Copied from Sheet1|40001
Mumbai|Table1( Copied from Sheet1|40003
Pune|Table1( Copied from Sheet1|40008
Loop for next row data in sheet 1

Sheet 3 ( after the run) pasted as values ( no formula which are in sheet 2 )
C|D|E form row 3
Delhi|Table1( Copied from Sheet1|40001
Mumbai|Table1( Copied from Sheet1|40003
Pune|Table1( Copied from Sheet1|40008
Delhi|Table2( Copied from Sheet1|40001
Mumbai|Table2( Copied from Sheet1|40003
Pune|Table2( Copied from Sheet1|40008
Delhi|Table3( Copied from Sheet1|40001
Mumbai|Table3( Copied from Sheet1|40003
Pune|Table3( Copied from Sheet1|40008


Appreciate a quick response.
Thanks for your support.
Regards
Manoj
See more 

Your reply

5 replies

ac3mark 9622 Posts Monday June 3, 2013Registration dateModeratorStatus June 22, 2018 Last seen - Mar 12, 2018 at 04:59 PM
0
Thank you
Go for it. Record the Macro, and post the MACRO code here. We will assist with the LOOPing. When we say we will assist, we mean we will help constructing a loop so that you understand it!
manojsaxena 3 Posts Sunday March 11, 2018Registration date March 13, 2018 Last seen - Mar 13, 2018 at 12:58 AM
Sub CopyPaste()
'
' CopyPaste Macro to copy first field from Sheet 1 into Sheet 2 selected cells and then copy all the selected cells in Sheet 2 base don filter to Sheet 3 at blank rows starting from selected column. Loop this till all values of Sheet 1 is exhusted.
'

'
' Loop to start
'Action 1-
Sheets("Sheet1").Select
Range("A2").Select
Application.CutCopyMode = False
Selection.Copy
' Action 2
Sheets("Sheet2").Select
Range("D3:D8").Select ' Select all filled in rows in column D
ActiveSheet.Paste
ActiveSheet.Range("$A$2:$D$8").AutoFilter Field:=1, Criteria1:="yes"
Range("B3:D8").Select ' I want this to be dynamic instead of fixed and shoudl select all whereever filter criteria is Yes
Application.CutCopyMode = False
Selection.Copy
' Action 3
Sheets("Sheet3").Select
Range("D3").Select
ActiveSheet.Paste

'Second iteration
'Action 1-
Sheets("Sheet1").Select
Range("A3").Select
Application.CutCopyMode = False
Selection.Copy
' Action 2
Sheets("Sheet2").Select
Sheets("Sheet2").Select
Range("D3:D8").Select ' Select all filled in rows in column D
ActiveSheet.Paste
ActiveSheet.Range("$A$2:$D$8").AutoFilter Field:=1, Criteria1:="yes"
Range("B3:D8").Select
Application.CutCopyMode = False
Selection.Copy

' Action 3
Sheets("Sheet3").Select
Range("D7").Select ' new blank line in sheet 3
ActiveSheet.Paste

'Third iteration
'Action 1-
Sheets("Sheet1").Select
Range("A4").Select
Application.CutCopyMode = False
Selection.Copy
'Action 2-
Sheets("Sheet2").Select
'ActiveSheet.Range("$A$2:$D$8").AutoFilter Field:=1
Range("D3:D8").Select ' Select all filled in rows in column D
ActiveSheet.Paste
ActiveSheet.Range("$A$2:$D$8").AutoFilter Field:=1, Criteria1:="yes"
Range("B3:D8").Select
Application.CutCopyMode = False
Selection.Copy
'Action 3-
Sheets("Sheet3").Select
Range("D11").Select
ActiveSheet.Paste
' And so on till all data of column A 2..of action 1 is exhusted ....
''Loop to start
End Sub
ac3mark 9622 Posts Monday June 3, 2013Registration dateModeratorStatus June 22, 2018 Last seen - Mar 13, 2018 at 04:38 PM
OK, there is method called the FOR loop.

Its structure is as follows:

for T = 0 to 100
msgbox("the Counter is set to: " & t)
next

or, if we wanted to skip numbers, we could do:
b=3
for t = 1 to 100 step B
msgbox("wqe are counting by " & b & "'s")
next

or, we can count down from a number. As in:
for t = 100 to 1 step -1
msgbox(t)
next

or, counting down by a number, as in:
for t = 100 to 1 step -2
'doyour loop
next


I hope this helps. Try some of these. Let us know if you get stuck on YOUR code!
manojsaxena 3 Posts Sunday March 11, 2018Registration date March 13, 2018 Last seen - Mar 13, 2018 at 09:52 PM
Sorry being new to VB it went on top of my head and could not fit in to my requirements.
ac3mark 9622 Posts Monday June 3, 2013Registration dateModeratorStatus June 22, 2018 Last seen - Mar 14, 2018 at 03:35 PM
Well, I am sorry you cannot grasp it. We are volunteers, who help when stuck. I DO NOT provide turnkey solutions, but try to get you to learn, so you can do it on your own.

Had you of applied the loop, we could have went to the next issue of looking for the CRITERIA text entry of "YES". But, since you cannto even grasp the loop, it will be hard to explain how to inspect a cell for QUALIFIED entries!

Keep coming back, as there are others that will write you code, and maybe they can pick it from here, as I will not spend time writing code, to have the SCOPE of work change when it does't do exactly what you want, and you have no knowledge for yourself on how to arrange it so it does fit your model.

I hope you understand.
Respond to ac3mark